Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

19Apr/160
Manage Traces in SQL Server

Quick notes on managing traces in SQL Server:

View all running traces:

SELECT * FROM sys.traces

Disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = 2 , @status = 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Filed under: SQL No Comments
9Feb/150
Find and Delete Running SQL Trace

Yesterday we had a long running trace take up too much space on disk. To find the running trace, look for the location of the file to identify your trace, and get the id:

SELECT * FROM sys.traces

Next, disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = # , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Have fun with traces, but make sure to stop them before you go home for the weekend!

Filed under: SQL No Comments
12Dec/140
Performance Tuning in SQL Server

I wanted to capture a few notes on performance tuning in SQL Server.

CPU and memory:

select * from sys.dm_os_performance_counters
where counter_name in ('Batch Requests/sec', 'SQL Compilations/sec' , 'SQL Re-Compilations/sec', 'Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base')

-- these counters accumulate since server startup, so we need to calculate usage over time.
-- for example, over the past 10 seconds (I'd expect to see this lower than 100, but that is only a guess until we learn how this server behaves - it depends on hardware, CPU type, etc.):

DECLARE @BatchRequests BIGINT;
SELECT @BatchRequests = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio                                                                                                          ';
WAITFOR DELAY '00:00:10';
SELECT (cntr_value - @BatchRequests) / 10 AS 'Buffer cache hit ratio                                                                                                          '
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio

-- The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

Page Life Expectancy:

SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Wait stats:

SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats

Reference: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

 

Filed under: SQL No Comments
12Dec/140
Monitoring SQL AlwaysOn

SQL AlwaysOn offers DMVs that make monitoring simple.

use master
go
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_networks
go
SELECT SERVERPROPERTY ('IsHadrEnabled');
go
select * from sys.dm_hadr_availability_group_states

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states

SELECT * FROM sys.dm_xe_objects WHERE name LIKE '%hadr%'

 

Filed under: SQL No Comments
7Jun/140
SQL Query for Recently Changed Objects

These may be useful to determine when objects have been changed.  We might infer from this info when was the last formal deployment of a database.

 

-- get most recently changed object of each type

SELECT  o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM    sys.objects o

JOIN(SELECT    type

,'LateDate' = MAX(modify_date)

FROM      sys.objects

WHERE     is_ms_shipped = 0

AND OBJECT_SCHEMA_NAME(object_id) <> 'tSQLt'

GROUP BY  type

) xo

ON o.type = xo.type

AND o.modify_date = xo.LateDate

ORDER BY o.modify_date DESC

,o.type

,o.name

 

-- get 20 most recently changed objects

SELECT TOP 20

o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

ORDER BY o.modify_date DESC

 

-- get 20 most recently changed objects that are not tSQLt or tSQLt tests

SELECT TOP 20

SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,o.name

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

AND OBJECT_SCHEMA_NAME(o.object_id) NOT LIKE 'Z%'

AND OBJECT_SCHEMA_NAME(o.object_id) <> 'tSQLt'

ORDER BY o.modify_date DESC

 

Filed under: SQL, Windows No Comments
30May/140
Fix an SQL Database Marked Suspect

If you have a database marked suspect, and do not have a backup to restore from, you can attempt to repair the database. This may lead to data loss and is most certainly not the best option (see: database backup).

Make sure there are no open connections to the database - stop all services that might attempt a connection. Run sp_who2 to check on open connections.

Run the commands one at a time and fix any errors as they appear:

EXEC sp_resetstatus 'DatabaseName';
ALTER DATABASE DatabaseName SET EMERGENCY;
DBCC CHECKDB ('DatabaseName');
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE DatabaseName SET MULTI_USER;

Restoring from a backup is the better way to fix this issue.

Filed under: SQL, Windows No Comments
22May/140
Purge Backup History from SQL msdb Database

I just about ran out of space on my Data partition and discovered that the msdb database was over 114 GB! In particular, the backupfile table had over 107,000,000 rows which took up over 70 GB. Other backup-related tables took up the rest of the space. This is a real problem. The normal command I would run to purge backup history did not work because it uses transactions and would fill up TLog. So I had to run the sp in a look one day at a time:
SET NOCOUNT ON
DECLARE @purge_date DATETIME
DECLARE @cutoff_date DATETIME

-- Set purge cutoff to n days back
set @cutoff_date = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-180,0)

PRINT 'Start Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)
PRINT 'Purge backup history before Cutoff Date: ' +
CONVERT(VARCHAR(10),@cutoff_date,121)

WHILE 1 = 1
BEGIN

SET @purge_date = null

-- Find date of oldest backup set
SELECT @purge_date = DATEADD(dd,DATEDIFF(dd,0,MIN(backup_finish_date))+1,0) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date <= @cutoff_date

IF @purge_date is null or @purge_date > @cutoff_date
BEGIN
PRINT 'Purge backup history complete through: '+
CONVERT(VARCHAR(10),@cutoff_date ,121)
BREAK
END

PRINT CHAR(10)+CHAR(13) +
'Purging backup history before: ' +
CONVERT(VARCHAR(10),@purge_date,121) +CHAR(10)+CHAR(13)

SELECT [Backup Sets to be Deleted Count ] = COUNT(*) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date < @purge_date

EXEC msdb.dbo.sp_delete_backuphistory @purge_date

END

PRINT 'End Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)

 

Filed under: SQL, Windows No Comments
10Feb/140
Query for the Size of All Databases in SQL

If you need to see the size of all databases on an SQL server, here is a helpful query, one I always seem to need to write by memory:

SELECT d.name AS Name,
(SUM(mf.size) * 8 / 1024 / 1024) AS Size_GB
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- skip system databases
GROUP BY d.name
ORDER BY d.name

 

Filed under: SQL, Windows No Comments
13Aug/130
SQL 2008 R2: Cannot Bring Database Back Online

I had a terribly stressful evening at work last night. I brought a database offline to move a file and when I tried to bring the database back online I received the following error:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Nothing I tried worked and a Google search was not helpful. Here is what happened:

I made a mistake right from the beginning, I should have moved the file before I set the database offline, but that is not what caused the problem:

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

As soon as I tried to set the database online, I received the error message: User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.

I did not think this was a permissions problem. At first I chased the part of the error that the database is not in a state that allows access checks. This led me nowhere because it makes sense that the database was not in a state that allows access checks because it has been set offline.

So a coworker looked at the file I moved. He discovered that file did not inherit permissions from the parent folder and the permissions were wrong. As soon as I reset permissions on the file I was able to set the database back online.

This was a frustrating problem, but I learned something I can add to my toolbox. Here is what I will do in the future:

ALTER DATABASE DatabaseName MODIFY FILE 
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

-- reset permissions on the file...

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

Filed under: SQL, Windows No Comments
11Apr/13Off
Move Data to Another SQL Server in Batches

I ran into a problem where I was unable to move data from one database to another in one INSERT INTO statement. I used the statement below to cut up the data into more manageable batches. This script allowed me to reliably move 5 million rows:

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempHolding]') AND type in (N'U'))

DR0P TABLE TempHolding
SELECT Id INTO TempHolding
FROM Databasename.Schema.TableName

WHILE EXISTS(SELECT TOP 1 1 FROM TempHolding)
BEGIN
SELECT TOP 50000 Id INTO TempStaging FROM TempHolding
INSERT INTO DestinationDatabaseName.Schema.TableName
SELECT t.* FROM Databasename.Schema.TableName t
JOIN TempStaging m ON m.Id = t.Id

DELETE j FROM TempHolding j WHERE EXISTS (SELECT j FROM TempStaging p WHERE p.Id = j.Id)
DR0P TABLE TempStaging

END

DR0P TABLE TempHolding

Note: I had to change DR0P table into DR0P table because of a filter on my text editor.

Filed under: SQL, Windows Comments Off