September 4, 2013 at 11:13 am
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'archer' and state = 0)
BEGIN
use archer
SET NOCOUNT ON
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @command nvarchar(4000)
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do
-- Open the cursor.
OPEN partitions
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag
IF @@FETCH_STATUS < 0 BREAK
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
EXEC (@command)
PRINT N'Executed: ' + @command
END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions
-- Drop the temporary table.
DROP TABLE #work_to_do;
END
It still executes even if the state of the archer database is 1. I don't know what is wrong. Can anyone help me?
Thanks a bunch.
Patti
September 4, 2013 at 12:26 pm
Try this:
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
BEGIN
...
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2013 at 1:24 pm
I have added what was suggested:
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
BEGIN
use archer
SET NOCOUNT ON
END
It looks good until you execute and I get this error:
Msg 954, Level 14, State 1, Line 3
The database "Archer" cannot be opened. It is acting as a mirror database.
September 4, 2013 at 1:33 pm
Patti Johnson (9/4/2013)
I have added what was suggested:IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
BEGIN
use archer
SET NOCOUNT ON
END
It looks good until you execute and I get this error:
Msg 954, Level 14, State 1, Line 3
The database "Archer" cannot be opened. It is acting as a mirror database.
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND
(mirroring_role IS NULL OR mirroring_role <> 2))
BEGIN
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2013 at 1:45 pm
Thank you so much for your help. I've been digging and digging so I appreciate your time.
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND
(mirroring_role IS NULL OR mirroring_role <> 2))
BEGIN
use archer
SET NOCOUNT ON
END
Msg 954, Level 14, State 1, Line 6
The database "Archer" cannot be opened. It is acting as a mirror database.
September 4, 2013 at 1:56 pm
Patti Johnson (9/4/2013)
Thank you so much for your help. I've been digging and digging so I appreciate your time.IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND
(mirroring_role IS NULL OR mirroring_role <> 2))
BEGIN
use archer
SET NOCOUNT ON
END
Msg 954, Level 14, State 1, Line 6
The database "Archer" cannot be opened. It is acting as a mirror database.
Hmm, OK, let's ignore any db that is involved in mirroring then:
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND
mirroring_role IS NULL)
BEGIN
use archer
SET NOCOUNT ON
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2013 at 2:18 pm
IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND
mirroring_role IS NULL)
BEGIN
use archer
SET NOCOUNT ON
END
Msg 954, Level 14, State 1, Line 6
The database "Archer" cannot be opened. It is acting as a mirror database.
:crazy:
September 4, 2013 at 4:02 pm
It does not help how many conditions you add to the IF statement. The USE statement is interpreted at compile time, why you always get the error. Here is a quick example:
IF 1 = 9
BEGIN
USE nosuchdb
END
Output:
Msg 911, Level 16, State 1, Line 3
Database 'nosuchdb' does not exist. Make sure that the name is entered correctly.
One workaround is to wrap it all in EXEC(), but that would make the code impossible to read and maintain. Another is put this in a stored procedure, which you then can call with:
IF databasepropertyex(...) etc
BEGIN
EXEC archer.dbo.do_some_reindexingstuff
END
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 5, 2013 at 6:21 am
Thank you. I will try that and get back with you.
September 5, 2013 at 11:42 am
Is there a way to make this stored procedure able to provide a parameter like the database name? I want to be able to provide the database name and make it so that the stored procedure can be used against other databases besides Archer.
September 5, 2013 at 3:58 pm
Yes and no.
That is, you can put your procedure in master and call it sp_somename. Then you can invoke it as
EXEC archer..sp_somename
The procedure will then execute in the context of the database you specified.
However, this is not supported and it could break, for instance if Microsoft ships a system procedure with name you use. Or it just stops working.
Note here that the procedure name must start with sp_.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 6, 2013 at 12:06 pm
That is very good news. I found a way of doing the procedure not knowing that you can do it the way you had mentioned. I will try it your way now and determine which is better. Thanks alot for your assistance.
Patti
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply