Declare @MyDatabase Varchar(20)
Declare @MySQL Varchar(100)
declare MyCursor Cursor for SELECT RTRIM(name) DatabaseName
from master..sysdatabases
where status not in (512, 528)
and dbid > 4
Open MyCursor
Fetch next from MyCursor into @MyDatabase
While @@FETCH_STATUS >= 0
Begin
--Set @MySQL = 'ALTER DATABASE [' + @MyDatabase + '] SET ENABLE_BROKER WITH NO_WAIT' -- Enable Service Broker
Set @MySQL = 'ALTER DATABASE [' + @MyDatabase + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT' -- Change Page_Verify
--print (@MySQL)
exec(@MySQL)
Fetch next from MyCursor into @MyDatabase
End
Close MyCursor
Deallocate MyCursor
2010年12月29日 星期三
SQL Server: Query all database name
SELECT dbid, RTRIM(name) DatabaseName
From master..sysdatabases
Where status not in (512, 528)
and name not like 'temp%'
and dbid > 4
From master..sysdatabases
Where status not in (512, 528)
and name not like 'temp%'
and dbid > 4
SQL Server: Query all table name with their schema
SELECT RTRIM(TABLE_SCHEMA) SchemaName, RTRIM(TABLE_NAME) TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME
SQL Server: Query all orphaned user account in database
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)
IF @DBName IS NULL BREAK
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid
)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT * FROM #Results
where [Orphaned User] like '%rho%'
ORDER BY [Database Name], [Orphaned User]
drop TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)
IF @DBName IS NULL BREAK
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid
)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT * FROM #Results
where [Orphaned User] like '%rho%'
ORDER BY [Database Name], [Orphaned User]
drop TABLE #Results
SQL Server: Verify current Kerberos authenication status
--Authenicaiton mode
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
--Conneciton authenication mode
--select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
--Conneciton authenication mode
--select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections
SQL Server: List all database schema
USE Master
GO
declare @MyTable as Varchar(10)
declare @sql as Varchar(500)
declare MyCursor cursor for
SELECT name
FROM sys.Databases
where name like 'PDL%'
Open MyCursor
Fetch next from MyCursor into @MyTable
while @@FETCH_STATUS = 0
Begin
set @sql = ' use ' + @MyTable +
' SELECT Table_catalog,table_schema,table_name,table_type FROM information_schema.Tables where TABLE_SCHEMA <> ''dbo'''
exec(@SQL)
Fetch next from MyCursor into @MyTable
End
close MyCursor
deallocate MyCursor
GO
declare @MyTable as Varchar(10)
declare @sql as Varchar(500)
declare MyCursor cursor for
SELECT name
FROM sys.Databases
where name like 'PDL%'
Open MyCursor
Fetch next from MyCursor into @MyTable
while @@FETCH_STATUS = 0
Begin
set @sql = ' use ' + @MyTable +
' SELECT Table_catalog,table_schema,table_name,table_type FROM information_schema.Tables where TABLE_SCHEMA <> ''dbo'''
exec(@SQL)
Fetch next from MyCursor into @MyTable
End
close MyCursor
deallocate MyCursor
SQL Server: List all database triggers
SELECT trigger_name = name, trigger_owner = USER_NAME(uid), table_name = OBJECT_NAME(parent_obj),
isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
status = CASE OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') WHEN 1 THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects
WHERE type = 'TR'
isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
status = CASE OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') WHEN 1 THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects
WHERE type = 'TR'
SQL Server: List all marked startup SPs
Select StartupProc = name from sys.sysobjects where xtype = 'p'
and OBJECTPROPERTY(id, 'ExecIsStartup') = 1;
and OBJECTPROPERTY(id, 'ExecIsStartup') = 1;
2010年12月23日 星期四
SQL Server: Memory pressure
Back to "A DMV a Day"
It turns out that there are several known issues with ad-hoc plan caching in SQL Server 2005 RTM and SP1 that have been corrected in hot fixes that are rolled up in SQL Server 2005 SP2. This blog post discusses the issue in more detail.
Recently, I detected that a 32GB database server that I use was using between 12-13GB of memory in the plan cache. This was causing internal memory pressure, and causing Page Life Expectancy (PLE) to go down into the sub 200 range (which is bad).
These DMV queries will help you detect this.
-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'
-- Top 10 consumers of memory from Buffer Pool
SELECT TOP 10 type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts
Use this DMV query to find the offenders:
-- Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC
Microsoft has made changes in SP2 that will help prevent this plan cache bloating problem. In the meantime, if you have lots of ad-hoc plans with a usecount of one, you can use a little trick to keep them from going into the plan cache. Just append OPTION (RECOMPILE) to the end of your ad-hoc SQL statement, and that will tell SQL Server to not put it into the cache. Then, you have to run DBCC FREEPROCCACHE to clear out the contents of the cache server-wide. You can also use a more surgical approach by using DBCC FLUSHPROCINDB.
--Determine the id of your database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'yourdatabasename')
--Flush the procedure cache for your database
DBCC FLUSHPROCINDB (@intDBID)
It turns out that there are several known issues with ad-hoc plan caching in SQL Server 2005 RTM and SP1 that have been corrected in hot fixes that are rolled up in SQL Server 2005 SP2. This blog post discusses the issue in more detail.
Recently, I detected that a 32GB database server that I use was using between 12-13GB of memory in the plan cache. This was causing internal memory pressure, and causing Page Life Expectancy (PLE) to go down into the sub 200 range (which is bad).
These DMV queries will help you detect this.
-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'
-- Top 10 consumers of memory from Buffer Pool
SELECT TOP 10 type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts
Use this DMV query to find the offenders:
-- Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC
Microsoft has made changes in SP2 that will help prevent this plan cache bloating problem. In the meantime, if you have lots of ad-hoc plans with a usecount of one, you can use a little trick to keep them from going into the plan cache. Just append OPTION (RECOMPILE) to the end of your ad-hoc SQL statement, and that will tell SQL Server to not put it into the cache. Then, you have to run DBCC FREEPROCCACHE to clear out the contents of the cache server-wide. You can also use a more surgical approach by using DBCC FLUSHPROCINDB.
--Determine the id of your database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'yourdatabasename')
--Flush the procedure cache for your database
DBCC FLUSHPROCINDB (@intDBID)
SQL Server; Most expensive query record
--DBCC FREESYSTEMCACHE
--DBCC FREEPROCCACHE
SELECT TOP 100 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) Expensive_QUERY,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time/1000000 total_worker_time_in_S,
qs.last_worker_time/1000000 last_worker_time_in_S,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.max_elapsed_time/1000000 MAX_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- total logical reads
-- ORDER BY qs.total_logical_writes DESC -- total logical writes
-- ORDER BY qs.total_worker_time DESC -- total CPU time
--ORDER BY qs.last_worker_time DESC -- most recent CPU time
ORDER BY qs.max_elapsed_time DESC, qs.total_elapsed_time DESC, execution_count ASC-- max execution time
--ORDER by qs.last_elapsed_time DESC -- most recent exection time
--DBCC FREEPROCCACHE
SELECT TOP 100 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) Expensive_QUERY,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time/1000000 total_worker_time_in_S,
qs.last_worker_time/1000000 last_worker_time_in_S,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.max_elapsed_time/1000000 MAX_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- total logical reads
-- ORDER BY qs.total_logical_writes DESC -- total logical writes
-- ORDER BY qs.total_worker_time DESC -- total CPU time
--ORDER BY qs.last_worker_time DESC -- most recent CPU time
ORDER BY qs.max_elapsed_time DESC, qs.total_elapsed_time DESC, execution_count ASC-- max execution time
--ORDER by qs.last_elapsed_time DESC -- most recent exection time
SQL Server: Rebuild index
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
Declare @SQL Varchar(8000);
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ['+@Database+']; '
-- Ensure a USE statement has been executed first.
SET @SQL = @SQL + ' SET NOCOUNT ON; '
SET @SQL = @SQL + ' DECLARE @objectid int; '
SET @SQL = @SQL + ' DECLARE @indexid int; '
SET @SQL = @SQL + ' DECLARE @partitioncount bigint; '
SET @SQL = @SQL + ' DECLARE @schemaname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @objectname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @indexname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @partitionnum bigint; '
SET @SQL = @SQL + ' DECLARE @partitions bigint; '
SET @SQL = @SQL + ' DECLARE @frag float; '
SET @SQL = @SQL + ' 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.
SET @SQL = @SQL + ' SELECT '
SET @SQL = @SQL + ' object_id AS objectid, '
SET @SQL = @SQL + ' index_id AS indexid, '
SET @SQL = @SQL + ' partition_number AS partitionnum, '
SET @SQL = @SQL + ' avg_fragmentation_in_percent AS frag '
SET @SQL = @SQL + ' INTO #work_to_do '
SET @SQL = @SQL + ' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') '
SET @SQL = @SQL + ' WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; '
-- Declare the cursor for the list of partitions to be processed.
SET @SQL = @SQL + ' DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; '
-- Open the cursor.
SET @SQL = @SQL + ' OPEN partitions; '
-- Loop through the partitions.
SET @SQL = @SQL + ' WHILE (1=1) '
SET @SQL = @SQL + ' BEGIN; '
SET @SQL = @SQL + ' FETCH NEXT '
SET @SQL = @SQL + ' FROM partitions '
SET @SQL = @SQL + ' INTO @objectid, @indexid, @partitionnum, @frag; '
SET @SQL = @SQL + ' IF @@FETCH_STATUS < 0 BREAK; '
SET @SQL = @SQL + ' SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) '
SET @SQL = @SQL + ' FROM sys.objects AS o '
SET @SQL = @SQL + ' JOIN sys.schemas as s ON s.schema_id = o.schema_id '
SET @SQL = @SQL + ' WHERE o.object_id = @objectid; '
SET @SQL = @SQL + ' SELECT @indexname = QUOTENAME(name) '
SET @SQL = @SQL + ' FROM sys.indexes '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
SET @SQL = @SQL + ' SELECT @partitioncount = count (*) '
SET @SQL = @SQL + ' FROM sys.partitions '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
SET @SQL = @SQL + ' IF @frag < 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE''; '
SET @SQL = @SQL + ' IF @frag >= 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD''; '
SET @SQL = @SQL + ' IF @partitioncount > 1 '
SET @SQL = @SQL + ' SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10)); '
SET @SQL = @SQL + ' EXEC (@command); '
--Print (@command);
SET @SQL = @SQL + ' PRINT N''Executed: '' + @command; '
SET @SQL = @SQL + ' END; '
-- Close and deallocate the cursor.
SET @SQL = @SQL + ' CLOSE partitions; '
SET @SQL = @SQL + ' DEALLOCATE partitions; '
-- Drop the temporary table.
SET @SQL = @SQL + ' DROP TABLE #work_to_do; '
Print @SQL
EXECUTE(@SQL)
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
Declare @SQL Varchar(8000);
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ['+@Database+']; '
-- Ensure a USE
SET @SQL = @SQL + ' SET NOCOUNT ON; '
SET @SQL = @SQL + ' DECLARE @objectid int; '
SET @SQL = @SQL + ' DECLARE @indexid int; '
SET @SQL = @SQL + ' DECLARE @partitioncount bigint; '
SET @SQL = @SQL + ' DECLARE @schemaname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @objectname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @indexname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @partitionnum bigint; '
SET @SQL = @SQL + ' DECLARE @partitions bigint; '
SET @SQL = @SQL + ' DECLARE @frag float; '
SET @SQL = @SQL + ' 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.
SET @SQL = @SQL + ' SELECT '
SET @SQL = @SQL + ' object_id AS objectid, '
SET @SQL = @SQL + ' index_id AS indexid, '
SET @SQL = @SQL + ' partition_number AS partitionnum, '
SET @SQL = @SQL + ' avg_fragmentation_in_percent AS frag '
SET @SQL = @SQL + ' INTO #work_to_do '
SET @SQL = @SQL + ' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') '
SET @SQL = @SQL + ' WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; '
-- Declare the cursor for the list of partitions to be processed.
SET @SQL = @SQL + ' DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; '
-- Open the cursor.
SET @SQL = @SQL + ' OPEN partitions; '
-- Loop through the partitions.
SET @SQL = @SQL + ' WHILE (1=1) '
SET @SQL = @SQL + ' BEGIN; '
SET @SQL = @SQL + ' FETCH NEXT '
SET @SQL = @SQL + ' FROM partitions '
SET @SQL = @SQL + ' INTO @objectid, @indexid, @partitionnum, @frag; '
SET @SQL = @SQL + ' IF @@FETCH_STATUS < 0 BREAK; '
SET @SQL = @SQL + ' SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) '
SET @SQL = @SQL + ' FROM sys.objects AS o '
SET @SQL = @SQL + ' JOIN sys.schemas as s ON s.schema_id = o.schema_id '
SET @SQL = @SQL + ' WHERE o.object_id = @objectid; '
SET @SQL = @SQL + ' SELECT @indexname = QUOTENAME(name) '
SET @SQL = @SQL + ' FROM sys.indexes '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
SET @SQL = @SQL + ' SELECT @partitioncount = count (*) '
SET @SQL = @SQL + ' FROM sys.partitions '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
SET @SQL = @SQL + ' IF @frag < 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE''; '
SET @SQL = @SQL + ' IF @frag >= 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD''; '
SET @SQL = @SQL + ' IF @partitioncount > 1 '
SET @SQL = @SQL + ' SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10)); '
SET @SQL = @SQL + ' EXEC (@command); '
--Print (@command);
SET @SQL = @SQL + ' PRINT N''Executed: '' + @command; '
SET @SQL = @SQL + ' END; '
-- Close and deallocate the cursor.
SET @SQL = @SQL + ' CLOSE partitions; '
SET @SQL = @SQL + ' DEALLOCATE partitions; '
-- Drop the temporary table.
SET @SQL = @SQL + ' DROP TABLE #work_to_do; '
Print @SQL
EXECUTE(@SQL)
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
SQL Server: Steps to set up replication using backup files instead of snapshots
/*
Steps to set up replication using backup files instead of snapshots
1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.
2. Once the wizard has finished, right-click the new publication, choose Properties
and make sure that the "Allow initialization from backup files" is set to True
3. Disable the "Distribution clean up: distribution" job to make sure that no
commands are deleted from MSrepl_commands before the entire backup/restore operation
is complete
4. Create a full database backup to disk and make sure that the file is available for the
publisher until the entire replication setup is finished.
5. Transfer the backup file to the subscriber server and restore it there. If you do
regular backups to disk make sure to use one that was taken *after* the publication
was created. Also restore trans log backups taken after the full backup. The last backup
should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves
you time in the syncing process when the replication is being initialized.
6. Disable all triggers on the subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
7. Disable all constraints on subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable
the replication:
EXEC sp_addsubscription
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'F:\backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
publishing server. If you restored trans logs also the last translog file is what you
need to put here
9. Enable the "Distribution clean up: distribution" job again
*/
Steps to set up replication using backup files instead of snapshots
1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.
2. Once the wizard has finished, right-click the new publication, choose Properties
and make sure that the "Allow initialization from backup files" is set to True
3. Disable the "Distribution clean up: distribution" job to make sure that no
commands are deleted from MSrepl_commands before the entire backup/restore operation
is complete
4. Create a full database backup to disk and make sure that the file is available for the
publisher until the entire replication setup is finished.
5. Transfer the backup file to the subscriber server and restore it there. If you do
regular backups to disk make sure to use one that was taken *after* the publication
was created. Also restore trans log backups taken after the full backup. The last backup
should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves
you time in the syncing process when the replication is being initialized.
6. Disable all triggers on the subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
7. Disable all constraints on subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable
the replication:
EXEC sp_addsubscription
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'F:\backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
publishing server. If you restored trans logs also the last translog file is what you
need to put here
9. Enable the "Distribution clean up: distribution" job again
*/
SQL Server: Query problematic replication trasnactions
--Run following query in distribution databases:
sp_browsereplcmds @xact_seqno_start = '0x0003FDBA0006519B001400000000,', @xact_seqno_end = '0x0003FDBA0006519B001400000000';
--OR
select * from dbo.MSarticles
where article_id in
(
select article_id from MSrepl_commands where xact_seqno = '0x0003FDBA0006519B001400000000'
)
sp_browsereplcmds @xact_seqno_start = '0x0003FDBA0006519B001400000000,', @xact_seqno_end = '0x0003FDBA0006519B001400000000';
--OR
select * from dbo.MSarticles
where article_id in
(
select article_id from MSrepl_commands where xact_seqno = '0x0003FDBA0006519B001400000000'
)
SQL Server: Query all published articles
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksProductTran';
USE [AdventureWorks]
EXEC sp_helparticle
@publication = @publication;
GO
SET @publication = N'AdvWorksProductTran';
USE [AdventureWorks]
EXEC sp_helparticle
@publication = @publication;
GO
SQL Server: Simple loading query
set nocount on;
Declare @ROWS INT
Declare @ROW INT
Declare @Count INT
Select @ROWS = 50000000
Select @ROW = 0
Select @Count = 0
While @ROW < @ROWS
Begin
Select @ROW = @ROW + 1
Print @Row
End
Declare @ROWS INT
Declare @ROW INT
Declare @Count INT
Select @ROWS = 50000000
Select @ROW = 0
Select @Count = 0
While @ROW < @ROWS
Begin
Select @ROW = @ROW + 1
Print @Row
End
SQL Server: Fix sql server login after database migration
EXEC sp_change_users_login 'auto_fix', loginName
SQL Server: Gather SQL server installed edition information
SELECT SERVERPROPERTY('productversion') [Version], SERVERPROPERTY ('productlevel') [SP_Level], SERVERPROPERTY ('edition') [SQL_Edition]
SQL Server: Query database name and size information
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
SQL Server: Change System Default Database and Log file Location
Appendix: Change System Default Database and Log file Location
We can change all system and resource database to relocation as following steps:
1. Use Alter database to change database files and database log files to new location:
Execution following query:
use [master]
go
alter database [master] modify file (name = 'mastlog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\mastlog.ldf')
go
alter database [master] modify file (name = 'master', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\master.mdf')
go
alter database [model] modify file (name = 'modellog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\modellog.ldf')
go
alter database [model] modify file (name = 'modeldev', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\model.mdf')
go
alter database [msdb] modify file (name = 'MSDBLog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\MSDBlog.ldf')
go
alter database [msdb] modify file (name = 'MSDBData', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\MSDBData.mdf')
go
--alter database tempDB modify file (name = templog, filename = 'S:\SQLSERVER\TransactionLog\TempDB\templog.ldf')
--go
--alter database tempDB modify file (name = tempdev, filename = 'S:\SQLSERVER\Data\TempDB\tempdb.mdf')
--go
2. Copy all system database files and database log files to new location:
3. To active node, click start, all programs, MS SQL 2008, Configuration tools, SQL Server configuration manager:
4. In SQL server configuration manager, we need to modify all default database file location and log file location: Click SQL Server Services, right click SQL Server (Instance name) and select property, then go to Advanced tab:
• Change “Dump Directory” to S:\sqlserver\zystem\log\
• Change “Startup Parameters” to:
-dS:\SQLSERVER\Data\SystemDB\master.mdf;-eS:\SQLSERVER\Zystem\Log\ERRORLOG;-lS:\SQLSERVER\TransactionLog\SystemDB\mastlog.ldf
Click OK to leave this section
In the same SQL Server Configuration Manager, we also need to change SQL Agent’s log file location: Right click SQL Server Agent (Instance name), select properties.
• In Advanced tab, change “Dump Directory” to S:\SQLSERVER\Zystem\Log\
6. Click OK to leave this section. Do not copy or move SQL agent log file to the new location folder. After system reboot or failover, new configuration will apply to SQL server service.
We can change all system and resource database to relocation as following steps:
1. Use Alter database to change database files and database log files to new location:
Execution following query:
use [master]
go
alter database [master] modify file (name = 'mastlog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\mastlog.ldf')
go
alter database [master] modify file (name = 'master', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\master.mdf')
go
alter database [model] modify file (name = 'modellog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\modellog.ldf')
go
alter database [model] modify file (name = 'modeldev', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\model.mdf')
go
alter database [msdb] modify file (name = 'MSDBLog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\MSDBlog.ldf')
go
alter database [msdb] modify file (name = 'MSDBData', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\MSDBData.mdf')
go
--alter database tempDB modify file (name = templog, filename = 'S:\SQLSERVER\TransactionLog\TempDB\templog.ldf')
--go
--alter database tempDB modify file (name = tempdev, filename = 'S:\SQLSERVER\Data\TempDB\tempdb.mdf')
--go
2. Copy all system database files and database log files to new location:
3. To active node, click start, all programs, MS SQL 2008, Configuration tools, SQL Server configuration manager:
4. In SQL server configuration manager, we need to modify all default database file location and log file location: Click SQL Server Services, right click SQL Server (Instance name) and select property, then go to Advanced tab:
• Change “Dump Directory” to S:\sqlserver\zystem\log\
• Change “Startup Parameters” to:
-dS:\SQLSERVER\Data\SystemDB\master.mdf;-eS:\SQLSERVER\Zystem\Log\ERRORLOG;-lS:\SQLSERVER\TransactionLog\SystemDB\mastlog.ldf
Click OK to leave this section
In the same SQL Server Configuration Manager, we also need to change SQL Agent’s log file location: Right click SQL Server Agent (Instance name), select properties.
• In Advanced tab, change “Dump Directory” to S:\SQLSERVER\Zystem\Log\
6. Click OK to leave this section. Do not copy or move SQL agent log file to the new location folder. After system reboot or failover, new configuration will apply to SQL server service.
SQL Server: Installation recovery from failure previous attempt
You cannot update a SQL Server 2008 R2 failover instance
View products that this article applies to.
Expand all | Collapse all
Symptoms
Consider the following scenario:
You install a new SQL Server 2008 R2 Failover cluster.
When you configure the cluster instance, you use an IP address that is already being used. This results in a failure in feature configuration for that instance
You try unsuccessfully to repair or uninstall that instance, and you leave the failed instance as it is.
You install a new instance of a SQL Server 2008 R2 Failover cluster.
The new instance is installed successfully and is put into production.
In this scenario, if you try to update the new instance with a cumulative update or a service pack, the setup fails. Additionally, you receive the following error message:
TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:
A failure was detected for a previous installation, patch, or repair for instance '' during configuration for features [SQL_Replication_Core_Inst,SQL_Engine_Core_Inst,MPT_AGENT_CORE_CNI,]. In order to apply this patch package (), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.
A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_Engine_Core_Shared,CommonFiles,SQL_WRITER,SQL_Browser_Redist,]. In order to apply this patch package (), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.
Error code 0x84B20001.
Back to the top
Cause
The failure of the original instance sets the ConfigurationState setting of various Shared Features to a failed update state in the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState
When an update is applied, it checks this registry key and returns an error if any feature is in a failed state.
View products that this article applies to.
Expand all | Collapse all
Symptoms
Consider the following scenario:
You install a new SQL Server 2008 R2 Failover cluster.
When you configure the cluster instance, you use an IP address that is already being used. This results in a failure in feature configuration for that instance
You try unsuccessfully to repair or uninstall that instance, and you leave the failed instance as it is.
You install a new instance of a SQL Server 2008 R2 Failover cluster.
The new instance is installed successfully and is put into production.
In this scenario, if you try to update the new instance with a cumulative update or a service pack, the setup fails. Additionally, you receive the following error message:
TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:
A failure was detected for a previous installation, patch, or repair for instance '
A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_Engine_Core_Shared,CommonFiles,SQL_WRITER,SQL_Browser_Redist,]. In order to apply this patch package (
Error code 0x84B20001.
Back to the top
Cause
The failure of the original instance sets the ConfigurationState setting of various Shared Features to a failed update state in the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState
When an update is applied, it checks this registry key and returns an error if any feature is in a failed state.
2010年12月22日 星期三
SQL Server: Show SQL server service memory usage
WITH CacheSums
As (SELECT 1 As OrderBy,
objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
UNION ALL
SELECT 2 AS OrderBy,
'Total' AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans)
SELECT CacheType, [Total Plans], [Total MBs], [Avg Use Count], [Total MBs - USE Count 1]
FROM CacheSums
ORDER BY OrderBy, [Total MBs - USE Count 1] DESC
As (SELECT 1 As OrderBy,
objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
UNION ALL
SELECT 2 AS OrderBy,
'Total' AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans)
SELECT CacheType, [Total Plans], [Total MBs], [Avg Use Count], [Total MBs - USE Count 1]
FROM CacheSums
ORDER BY OrderBy, [Total MBs - USE Count 1] DESC
SQL Server: Getting User Session information
--Getting User Session information in SQL server 2005/2008
--In SQL server 2005/2008 if you want to check all SQL server sessions following is the command for viewing this;
select * from sys.dm_exec_sessions;
--But the above command will show the user sessions and as well as the system sessions, if you want to only see the user connections then following is the command;
select * from sys.dm_exec_connections;
--you can also get the session id from the below command and match it with the session_id column of the 2 or 1 command for viewing the specific session information.
Select @@SPID;
--OR
select * from master..sysprocesses
select * from sys.dm_exec_requests
--In SQL server 2005/2008 if you want to check all SQL server sessions following is the command for viewing this;
select * from sys.dm_exec_sessions;
--But the above command will show the user sessions and as well as the system sessions, if you want to only see the user connections then following is the command;
select * from sys.dm_exec_connections;
--you can also get the session id from the below command and match it with the session_id column of the 2 or 1 command for viewing the specific session information.
Select @@SPID;
--OR
select * from master..sysprocesses
select * from sys.dm_exec_requests
SQL Server: Shrink database file in step
--this is good for large database after intensive DML query to claim free disk space.
--it can show the step progress for user.
--remember to alter index and, if possible, do the update stats right after this process.
-- Shrink_DB_File.sql
/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'PDL_Data'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 50
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
exec ( @sql )
print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
--it can show the step progress for user.
--remember to alter index and, if possible, do the update stats right after this process.
-- Shrink_DB_File.sql
/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'PDL_Data'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 50
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
exec ( @sql )
print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
SQL Server: 2008 R2 Performacne counters
-- Hardware information from SQL Server 2008 R2
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
affinity_type_desc, time_source_desc,
process_user_time_ms,
CAST (CAST(process_user_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL User Time],
process_kernel_time_ms,
CAST (CAST(process_kernel_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL Kernel Time],
sqlserver_start_time
FROM sys.dm_os_sys_info;
-- Get CPU Utilization History for last 30 minutes (SQL 2008 and 2008 R2)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%') AS x
) AS y
ORDER BY record_id DESC;
-- Look at aggregated managed instance health (msdb)
SELECT mi_count, mi_healthy_count, mi_unhealthy_count, mi_over_utilize_count, mi_under_utilize_count,
mi_on_over_utilized_computer_count, mi_on_under_utilized_computer_count,
mi_with_files_on_over_utilized_volume_count, mi_with_files_on_over_utilized_volume_count,
mi_with_over_utilized_file_count, mi_with_over_utilized_file_count,
mi_with_over_utilized_processor_count, mi_with_under_utilized_processor_count
FROM msdb.dbo.sysutility_ucp_aggregated_mi_health;
-- Look at managed instance information (msdb)
SELECT *
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_mi_health AS mih
ON mi.instance_name = mih.mi_name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_instances AS inst
ON mi.instance_name = inst.Name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_computers AS comp
ON mi.virtual_server_name = comp.virtual_server_name;
-- Look at hardware info for managed instances (sysutility_mdw)
SELECT TOP(25)virtual_server_name ,physical_server_name,is_clustered_server,
num_processors ,cpu_name,cpu_caption,cpu_architecture,
cpu_max_clock_speed,cpu_clock_speed,l2_cache_size,l3_cache_size,
percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.latest_computers;
-- Look at server level CPU utilization (sysutility_mdw)
SELECT TOP(1000) physical_server_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 1
ORDER BY processing_time DESC;
-- Look at instance level CPU utilization (sysutility_mdw)
SELECT TOP(1000) server_instance_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 3
ORDER BY processing_time DESC;
-- Look at most recent instance level CPU utilization (sysutility_mdw)
SELECT TOP(25)server_instance_name, instance_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_instance_cpu_utilization
ORDER BY server_instance_name;
-- Look at hardware information (sysutility_mdw)
SELECT TOP(25) physical_server_name, num_processors, cpu_name, cpu_caption,
cpu_architecture, cpu_max_clock_speed,cpu_clock_speed, l2_cache_size,
l3_cache_size, server_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_computer_cpu_memory_configuration
ORDER BY [Rank];
-- Look at wait types and categories
SELECT category_name, wait_type, ignore
FROM sysutility_mdw.core.wait_types_categorized
ORDER BY category_name;
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
affinity_type_desc, time_source_desc,
process_user_time_ms,
CAST (CAST(process_user_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL User Time],
process_kernel_time_ms,
CAST (CAST(process_kernel_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL Kernel Time],
sqlserver_start_time
FROM sys.dm_os_sys_info;
-- Get CPU Utilization History for last 30 minutes (SQL 2008 and 2008 R2)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%
) AS y
ORDER BY record_id DESC;
-- Look at aggregated managed instance health (msdb)
SELECT mi_count, mi_healthy_count, mi_unhealthy_count, mi_over_utilize_count, mi_under_utilize_count,
mi_on_over_utilized_computer_count, mi_on_under_utilized_computer_count,
mi_with_files_on_over_utilized_volume_count, mi_with_files_on_over_utilized_volume_count,
mi_with_over_utilized_file_count, mi_with_over_utilized_file_count,
mi_with_over_utilized_processor_count, mi_with_under_utilized_processor_count
FROM msdb.dbo.sysutility_ucp_aggregated_mi_health;
-- Look at managed instance information (msdb)
SELECT *
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_mi_health AS mih
ON mi.instance_name = mih.mi_name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_instances AS inst
ON mi.instance_name = inst.Name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_computers AS comp
ON mi.virtual_server_name = comp.virtual_server_name;
-- Look at hardware info for managed instances (sysutility_mdw)
SELECT TOP(25)virtual_server_name ,physical_server_name,is_clustered_server,
num_processors ,cpu_name,cpu_caption,cpu_architecture,
cpu_max_clock_speed,cpu_clock_speed,l2_cache_size,l3_cache_size,
percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.latest_computers;
-- Look at server level CPU utilization (sysutility_mdw)
SELECT TOP(1000) physical_server_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 1
ORDER BY processing_time DESC;
-- Look at instance level CPU utilization (sysutility_mdw)
SELECT TOP(1000) server_instance_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 3
ORDER BY processing_time DESC;
-- Look at most recent instance level CPU utilization (sysutility_mdw)
SELECT TOP(25)server_instance_name, instance_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_instance_cpu_utilization
ORDER BY server_instance_name;
-- Look at hardware information (sysutility_mdw)
SELECT TOP(25) physical_server_name, num_processors, cpu_name, cpu_caption,
cpu_architecture, cpu_max_clock_speed,cpu_clock_speed, l2_cache_size,
l3_cache_size, server_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_computer_cpu_memory_configuration
ORDER BY [Rank];
-- Look at wait types and categories
SELECT category_name, wait_type, ignore
FROM sysutility_mdw.core.wait_types_categorized
ORDER BY category_name;
SQL Server: Query all tables' size in database
drop table #tblResults
CREATE TABLE #tblResults
(
[name] nvarchar(100),
[rows] int,
[reserved] varchar(50),
[reserved_int] int default(0),
[data] varchar(100),
[data_int] int default(0),
[index_size] varchar(50),
[index_size_int] int default(0),
[unused] varchar(50),
[unused_int] int default(0)
)
-- Populate the temp table...
EXEC sp_MSforeachtable @command1= -- dark SP!
"INSERT INTO #tblResults
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused '?'"
-- Strip out the " KB" portion from the fields
UPDATE #tblResults SET
[reserved_int] = CAST(SUBSTRING([reserved], 1,
CHARINDEX(' ', [reserved])) AS int),
[data_int] = CAST(SUBSTRING([data], 1,
CHARINDEX(' ', [data])) AS int),
[index_size_int] = CAST(SUBSTRING([index_size], 1,
CHARINDEX(' ', [index_size])) AS int),
[unused_int] = CAST(SUBSTRING([unused], 1,
CHARINDEX(' ', [unused])) AS int)
-- Return the results...
SELECT * FROM #tblResults order by data_int desc
Drop table #tblResults
CREATE TABLE #tblResults
(
[name] nvarchar(100),
[rows] int,
[reserved] varchar(50),
[reserved_int] int default(0),
[data] varchar(100),
[data_int] int default(0),
[index_size] varchar(50),
[index_size_int] int default(0),
[unused] varchar(50),
[unused_int] int default(0)
)
-- Populate the temp table...
EXEC sp_MSforeachtable @command1= -- dark SP!
"INSERT INTO #tblResults
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused '?'"
-- Strip out the " KB" portion from the fields
UPDATE #tblResults SET
[reserved_int] = CAST(SUBSTRING([reserved], 1,
CHARINDEX(' ', [reserved])) AS int),
[data_int] = CAST(SUBSTRING([data], 1,
CHARINDEX(' ', [data])) AS int),
[index_size_int] = CAST(SUBSTRING([index_size], 1,
CHARINDEX(' ', [index_size])) AS int),
[unused_int] = CAST(SUBSTRING([unused], 1,
CHARINDEX(' ', [unused])) AS int)
-- Return the results...
SELECT * FROM #tblResults order by data_int desc
Drop table #tblResults
SQL Server: Update STSTS for all user databases
declare @DBName Varchar(50)
declare @SQL Varchar(2000)
declare MyCursor Cursor for
SELECT RTRIM(m.name)SysDBName
from master..sysdatabases m
where m.status not in (512, 528)
and m.name not like 'temp%'
and m.dbid > 4
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS = 0
begin
Set @SQL = 'Use [' + @DBName + ']; EXEC sp_updatestats;'
exec(@SQL)
Fetch next from MyCursor into @DBName
End
Close MyCursor
deallocate MyCursor
declare @SQL Varchar(2000)
declare MyCursor Cursor for
SELECT RTRIM(m.name)SysDBName
from master..sysdatabases m
where m.status not in (512, 528)
and m.name not like 'temp%'
and m.dbid > 4
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS = 0
begin
Set @SQL = 'Use [' + @DBName + ']; EXEC sp_updatestats;'
exec(@SQL)
Fetch next from MyCursor into @DBName
End
Close MyCursor
deallocate MyCursor
SQl Server: Performance Counters
--SQL Server Files IO, SQL Server 2005 and up only!
SELECT DB_NAME(database_id) AS 'Database Name',
file_id,
io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM sys.dm_io_virtual_file_stats(-1, -1)
WHERE num_of_reads > 0
AND num_of_writes > 0
--All time consuming task
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50
--CXPACKET parallelism process
SELECT wt.*,
st.text,
qp.query_plan
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_requests er
ON wt.waiting_task_address = er.task_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE wt.wait_type = 'CXPACKET'
ORDER BY wt.session_id
--Most time consuming task
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
--Or
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY max_wait_time_ms DESC
--It sounds like perhaps you were bottlenecked on I/O before, and you have improved your I/O situation, letting the CPUs work a little harder. I would not be too alarmed at 30-35% CPU. These queries may help you explore your I/O situation and your top wait stats in a little more deeply.
-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'
ORDER BY cpu_id;
-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';
-- Calculates average stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;
-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
SELECT DB_NAME(database_id) AS 'Database Name',
file_id,
io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM sys.dm_io_virtual_file_stats(-1, -1)
WHERE num_of_reads > 0
AND num_of_writes > 0
--All time consuming task
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50
--CXPACKET parallelism process
SELECT wt.*,
st.text,
qp.query_plan
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_requests er
ON wt.waiting_task_address = er.task_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE wt.wait_type = 'CXPACKET'
ORDER BY wt.session_id
--Most time consuming task
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
--Or
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY max_wait_time_ms DESC
--It sounds like perhaps you were bottlenecked on I/O before, and you have improved your I/O situation, letting the CPUs work a little harder. I would not be too alarmed at 30-35% CPU. These queries may help you explore your I/O situation and your top wait stats in a little more deeply.
-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'
ORDER BY cpu_id;
-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';
-- Calculates average stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;
-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
SQL Server: Enable table level CDC function
Declare @MyTable Varchar(100)
Declare Mycursor Cursor for
SELECT NAME
from sys.tables
where name in
('123','456','789')
and schema_id = 1
and type = 'U'
Open Mycursor
Fetch next from Mycursor into @MyTable
While @@FETCH_STATUS = 0
Begin
declare @tableName varchar(100)
set @tableName = @MyTable --replace with your Tablename
declare @primaryJoin varchar(max)
set @primaryJoin = ''
declare @SQL nvarchar(max)
set @SQL = ''
Set @SQL = 'exec sys.sp_cdc_enable_table '
Set @SQL = @SQL + ' @source_schema = ''dbo'','
Set @SQL = @SQL + ' @source_name = '+@MyTable+','
Set @SQL = @SQL + ' @role_name = NULL, '
Set @SQL = @SQL + ' @supports_net_changes = 1; '
print @SQL
Fetch next from Mycursor into @MyTable
End
Close MyCursor
Deallocate Mycursor
Declare Mycursor Cursor for
SELECT NAME
from sys.tables
where name in
('123','456','789')
and schema_id = 1
and type = 'U'
Open Mycursor
Fetch next from Mycursor into @MyTable
While @@FETCH_STATUS = 0
Begin
declare @tableName varchar(100)
set @tableName = @MyTable --replace with your Tablename
declare @primaryJoin varchar(max)
set @primaryJoin = ''
declare @SQL nvarchar(max)
set @SQL = ''
Set @SQL = 'exec sys.sp_cdc_enable_table '
Set @SQL = @SQL + ' @source_schema = ''dbo'','
Set @SQL = @SQL + ' @source_name = '+@MyTable+','
Set @SQL = @SQL + ' @role_name = NULL, '
Set @SQL = @SQL + ' @supports_net_changes = 1; '
print @SQL
Fetch next from Mycursor into @MyTable
End
Close MyCursor
Deallocate Mycursor
SQL Server: Use page level compression
Set nocount on
Declare @SQL Varchar(1000)
Declare @DBOTable Varchar(1000)
Drop table #MyTable
Create Table #MyTable
(
[object_name] Varchar(200),
[schema_name] Varchar(200),
[index_id] INT,
[partition_number] INT,
[size_with_current_compression_setting (KB)] BIGINT,
[size_with_requested_compression_setting (KB)] BIGINT,
[sample_size_with_current_compression_setting (KB)] BIGINT,
[sample_size_with_requested_compression_setting (KB)] BIGINT
);
Declare MyCursor Cursor For
select Name from sys.tables
where schema_id = 1 and parent_object_id = 0
and Type = 'U'
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
--Print @DBOTable
Set @SQL = ' INSERT INTO #MyTable EXEC sp_estimate_data_compression_savings '
Set @SQL = @SQL + ' @schema_name = ''dbo'','
Set @SQL = @SQL + ' @object_name = '''+ @DBOTable + ''','
Set @SQL = @SQL + ' @index_id = NULL, '
Set @SQL = @SQL + ' @partition_number = NULL, '
Set @SQL = @SQL + ' @data_compression = ''PAGE'' ;'
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Declare MyCursor Cursor for
select [object_name] from #MyTable
Where [size_with_requested_compression_setting (KB)] < ([size_with_current_compression_setting (KB)] * 0.9)
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
Set @SQL = ' ALTER TABLE dbo.' + @DBOTable + ' REBUILD WITH (DATA_COMPRESSION = PAGE) '
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Drop table #MyTable
Set nocount off
Declare @SQL Varchar(1000)
Declare @DBOTable Varchar(1000)
Drop table #MyTable
Create Table #MyTable
(
[object_name] Varchar(200),
[schema_name] Varchar(200),
[index_id] INT,
[partition_number] INT,
[size_with_current_compression_setting (KB)] BIGINT,
[size_with_requested_compression_setting (KB)] BIGINT,
[sample_size_with_current_compression_setting (KB)] BIGINT,
[sample_size_with_requested_compression_setting (KB)] BIGINT
);
Declare MyCursor Cursor For
select Name from sys.tables
where schema_id = 1 and parent_object_id = 0
and Type = 'U'
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
--Print @DBOTable
Set @SQL = ' INSERT INTO #MyTable EXEC sp_estimate_data_compression_savings '
Set @SQL = @SQL + ' @schema_name = ''dbo'','
Set @SQL = @SQL + ' @object_name = '''+ @DBOTable + ''','
Set @SQL = @SQL + ' @index_id = NULL, '
Set @SQL = @SQL + ' @partition_number = NULL, '
Set @SQL = @SQL + ' @data_compression = ''PAGE'' ;'
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Declare MyCursor Cursor for
select [object_name] from #MyTable
Where [size_with_requested_compression_setting (KB)] < ([size_with_current_compression_setting (KB)] * 0.9)
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
Set @SQL = ' ALTER TABLE dbo.' + @DBOTable + ' REBUILD WITH (DATA_COMPRESSION = PAGE) '
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Drop table #MyTable
Set nocount off
SQL Server: Use page level compression
Set nocount on
Declare @SQL Varchar(1000)
Declare @DBOTable Varchar(1000)
Drop table #MyTable
Create Table #MyTable
(
[object_name] Varchar(200),
[schema_name] Varchar(200),
[index_id] INT,
[partition_number] INT,
[size_with_current_compression_setting (KB)] BIGINT,
[size_with_requested_compression_setting (KB)] BIGINT,
[sample_size_with_current_compression_setting (KB)] BIGINT,
[sample_size_with_requested_compression_setting (KB)] BIGINT
);
Declare MyCursor Cursor For
select Name from sys.tables
where schema_id = 1 and parent_object_id = 0
and Type = 'U'
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
--Print @DBOTable
Set @SQL = ' INSERT INTO #MyTable EXEC sp_estimate_data_compression_savings '
Set @SQL = @SQL + ' @schema_name = ''dbo'','
Set @SQL = @SQL + ' @object_name = '''+ @DBOTable + ''','
Set @SQL = @SQL + ' @index_id = NULL, '
Set @SQL = @SQL + ' @partition_number = NULL, '
Set @SQL = @SQL + ' @data_compression = ''PAGE'' ;'
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Declare MyCursor Cursor for
select [object_name] from #MyTable
Where [size_with_requested_compression_setting (KB)] < ([size_with_current_compression_setting (KB)] * 0.9)
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
Set @SQL = ' ALTER TABLE dbo.' + @DBOTable + ' REBUILD WITH (DATA_COMPRESSION = PAGE) '
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Drop table #MyTable
Set nocount off
Declare @SQL Varchar(1000)
Declare @DBOTable Varchar(1000)
Drop table #MyTable
Create Table #MyTable
(
[object_name] Varchar(200),
[schema_name] Varchar(200),
[index_id] INT,
[partition_number] INT,
[size_with_current_compression_setting (KB)] BIGINT,
[size_with_requested_compression_setting (KB)] BIGINT,
[sample_size_with_current_compression_setting (KB)] BIGINT,
[sample_size_with_requested_compression_setting (KB)] BIGINT
);
Declare MyCursor Cursor For
select Name from sys.tables
where schema_id = 1 and parent_object_id = 0
and Type = 'U'
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
--Print @DBOTable
Set @SQL = ' INSERT INTO #MyTable EXEC sp_estimate_data_compression_savings '
Set @SQL = @SQL + ' @schema_name = ''dbo'','
Set @SQL = @SQL + ' @object_name = '''+ @DBOTable + ''','
Set @SQL = @SQL + ' @index_id = NULL, '
Set @SQL = @SQL + ' @partition_number = NULL, '
Set @SQL = @SQL + ' @data_compression = ''PAGE'' ;'
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Declare MyCursor Cursor for
select [object_name] from #MyTable
Where [size_with_requested_compression_setting (KB)] < ([size_with_current_compression_setting (KB)] * 0.9)
Open MyCursor
Fetch next from MyCursor into @DBOTable
While @@FETCH_STATUS = 0
Begin
Set @SQL = ' ALTER TABLE dbo.' + @DBOTable + ' REBUILD WITH (DATA_COMPRESSION = PAGE) '
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End
Close MyCursor
Deallocate MyCursor
Drop table #MyTable
Set nocount off
SQL Server: Drop user in certain domain
use [Master]
Set nocount on;
Declare @DBName varchar(20);
Declare @SQL Varchar(2000);
Declare @DropDomain Varchar(256) = 'LIKE_ME\'; --domain user
Declare MyCursor Cursor for
select RTRIM(name) as DatabaseName
from sys.databases
where database_id > 4
and state = 0
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS >= 0
Begin
set @SQL = 'use [' + @DBName + '];'
print(@SQL)
execute(@SQL)
Create TABLE ##DatabaseRoleMember(DBRole varchar(50),MemberName varchar(50),MemberSID varchar(2000));
Create TABLE ##DropUnuseMember(SQLScript Varchar(1000));
Set @SQL = 'use [' + @DBName + ']; Insert into ##DatabaseRoleMember execute sp_helprolemember; '
Print(@SQL)
Execute(@SQL)
set @SQL = 'use [' + @DBName + ']; Insert into ##DropUnuseMember select ''use [' + @DBName + ']; DROP USER ['' + MemberName + ''];'' from ##DatabaseRoleMember where Upper(LEFT(MemberName,8)) = '''+@DropDomain+''';'
Print(@SQL)
Execute(@SQL)
Drop TABLE ##DatabaseRoleMember;
Declare MyCursor2 Cursor for
Select * from ##DropUnuseMember
Open MyCursor2
Fetch next from MyCursor2 into @SQL
While @@FETCH_STATUS >= 0
Begin
Print(@SQL)
Execute(@SQL)
Fetch next from MyCursor2 into @SQL
End
Close MyCursor2
Deallocate MyCursor2
Drop TABLE ##DropUnuseMember
Fetch next from MyCursor into @DBName
End
Close MyCursor
Deallocate MyCursor
Set nocount off;
Set nocount on;
Declare @DBName varchar(20);
Declare @SQL Varchar(2000);
Declare @DropDomain Varchar(256) = 'LIKE_ME\'; --domain user
Declare MyCursor Cursor for
select RTRIM(name) as DatabaseName
from sys.databases
where database_id > 4
and state = 0
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS >= 0
Begin
set @SQL = 'use [' + @DBName + '];'
print(@SQL)
execute(@SQL)
Create TABLE ##DatabaseRoleMember(DBRole varchar(50),MemberName varchar(50),MemberSID varchar(2000));
Create TABLE ##DropUnuseMember(SQLScript Varchar(1000));
Set @SQL = 'use [' + @DBName + ']; Insert into ##DatabaseRoleMember execute sp_helprolemember; '
Print(@SQL)
Execute(@SQL)
set @SQL = 'use [' + @DBName + ']; Insert into ##DropUnuseMember select ''use [' + @DBName + ']; DROP USER ['' + MemberName + ''];'' from ##DatabaseRoleMember where Upper(LEFT(MemberName,8)) = '''+@DropDomain+''';'
Print(@SQL)
Execute(@SQL)
Drop TABLE ##DatabaseRoleMember;
Declare MyCursor2 Cursor for
Select * from ##DropUnuseMember
Open MyCursor2
Fetch next from MyCursor2 into @SQL
While @@FETCH_STATUS >= 0
Begin
Print(@SQL)
Execute(@SQL)
Fetch next from MyCursor2 into @SQL
End
Close MyCursor2
Deallocate MyCursor2
Drop TABLE ##DropUnuseMember
Fetch next from MyCursor into @DBName
End
Close MyCursor
Deallocate MyCursor
Set nocount off;
SQL Server: List all replicate databases and their replicate tables
Create Table ##PublisheTable
( DBName varchar(256),
TName varchar(256)
)
declare @DBName Varchar(20)
declare @SQL Varchar(2000)
--declare @SQL Varchar(2000)
--Create Table ##VSDESC
--( DatabaseName Varchar(100), Module Varchar(50), [Version] Varchar(50), Since Date, ReleaseLable Varchar(10), LastTransactionDate Date);
declare MyCursor Cursor for
SELECT RTRIM(m.name)SysDBName
from master..sysdatabases m
where m.status not in (512, 528)
and m.dbid > 4
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS = 0
begin
SET @SQL = 'USE [' + @DBName + ']; INSERT INTO ##PublisheTable Select DB_NAME() as DBName, Name TName from sys.tables '
SET @SQL = @SQL + 'where schema_id = 1 '
SET @SQL = @SQL + 'and is_published = 1 '
EXEC(@sql)
Fetch next from MyCursor into @DBName
End
Close MyCursor
deallocate MyCursor
Select * from ##PublisheTable;
Drop Table ##PublisheTable;
( DBName varchar(256),
TName varchar(256)
)
declare @DBName Varchar(20)
declare @SQL Varchar(2000)
--declare @SQL Varchar(2000)
--Create Table ##VSDESC
--( DatabaseName Varchar(100), Module Varchar(50), [Version] Varchar(50), Since Date, ReleaseLable Varchar(10), LastTransactionDate Date);
declare MyCursor Cursor for
SELECT RTRIM(m.name)SysDBName
from master..sysdatabases m
where m.status not in (512, 528)
and m.dbid > 4
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS = 0
begin
SET @SQL = 'USE [' + @DBName + ']; INSERT INTO ##PublisheTable Select DB_NAME() as DBName, Name TName from sys.tables '
SET @SQL = @SQL + 'where schema_id = 1 '
SET @SQL = @SQL + 'and is_published = 1 '
EXEC(@sql)
Fetch next from MyCursor into @DBName
End
Close MyCursor
deallocate MyCursor
Select * from ##PublisheTable;
Drop Table ##PublisheTable;
Query SQL Server job history (2005/2008/2008R2)
select @@SERVERNAME as SQL_Server, job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where h.step_id = 0
) t
) t
order by job_name, run_datetime
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where h.step_id = 0
) t
) t
order by job_name, run_datetime
訂閱：
文章 (Atom)