Cycle Those Logs!

7 08 2015

The SQL Server Error log is an extremely useful troubleshooting tool and often the first stop when trying to identify any issues. Beyond troubleshooting a DBA should be reviewing that log file on a weekly basis at a minimum, preferably daily on critical systems.

The problem is that by default the SQL Server Error log only rolls its log over when the SQL Server service restarts. Given that the goal on any production database is to keep it up and running, we are left with a log file that has months worth of data in it. So when things to go down the log file is to large to even open let alone find useful information in.

Microsoft provides a stored procedure for us to deal with this problem, sp_cycle_errorlogs. Executing this command causes the SQL Server error log to roll over and start a new log file.

Create a job that runs daily at midnight that executes the stored procedure sp_cycle_errorlogs. A log file will be created for each day, with that it is important to configure the number of logs to be retained.

Expand the Management tree in SSMS and right-click on SQL Server Logs node and select Configure.
Select the check box to limit the number of error log files and set the value to the number of days you want to keep logs local for. Typically I will configure this value to 30.

Advertisements




SQL Server Reporting Services – Friendly Email Name / Alias

8 04 2015

Recently when cutting over a reporting server it was asked if it were possible to assign a friendly name to the email address. There is no input text box for this in reporting services configuration and I honestly really had no idea.

Time to turn to Google.

As it turns out it is as simple as formatting the from email address in the SSRS email configuration.

Instead of just plopping in an email address and calling it a day, you can include the friendly name as well!

“Something Nice” something@abc.com

Formatting the email address in this way will show the friendly name, “Something Nice”, to recipients of the email instead of just the email address.





SQL Server – Cursor Example

25 11 2014

Cursors in SQL Server allow developers to iterate through a collection of values, normally based off a select statement. This is very useful for scripts that must apply to many items at once. More often than not that is iterating through database names to make changes to each database on an instance. Here is an example of how to iterate through all of the databases on an instance using cursors.


DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print @name;
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor





List all Databases and Their Associated Files

30 09 2014

Sometimes we need a clear picture for a project or perhaps we have just taken over a new server to administer. We need to know certain things like what the heck is going on with all of these databases and why is the disk full?

select d.name, d.recovery_model_desc , d.is_encrypted, mf.name, mf.physical_name, ((mf.size * 8) / 1024) as 'File Size (MB)', mf.type_desc as 'type' FROM sys.databases d
Inner join sys.master_files mf on d.database_id = mf.database_id





Move Database Files Script

10 09 2014

Been a while since my last post, it has been an incredibly busy past couple of months. Getting back into the swing of things I wrote this script to make moving database files around a little easier and less time consuming. This was developed in conjunction with a project I am working on where there are hundreds of SQL Servers and each of  them has their database files scattered into the winds. It literally looks like someone took all the database files loaded them into a shotgun and blasted the server with them. Moving the databases to standardized locations is one of the many tasks I have to perform on all of these servers and moving all of those files was just to much to do by hand.


/*
Title: Move Database Files
Created: September 8th, 2014
By: Christopher Provolt

Input: Change the values for the following variables
@new_data_file_path - Path to where database data files should be moved to
@new_log_file_path - Path to where database log files should be moved to

Notes:
Ensure that the paths are set correctly prior to running scripts
Change output to text (Ctrl + T or Query > Results To > Results To Text)
Last Update
Name:
Date:

Change Log
------------------------------------------------------------------------------------------
8/9/2014 - Initial script
*/

set nocount on;

declare @new_data_file_path varchar(300)
declare @new_log_file_path varchar(300)

/* Define the new datapaths for the files to be moved to
*/
set @new_data_file_path = LOWER('<New Data File Path, Nvarchar(300),e:\mssql\data>')
set @new_log_file_path = LOWER('<New Log File Path, Nvarchar(300),f:\mssql\logs>')

/* Dynamic table to hold information on database files and their locations
we will use this data to create the required move scripts
*/

declare @info table
(
id int primary key identity,
database_id int,
dbname varchar (50),
virtual_file_name varchar(50),
type_desc varchar(10),
db_file_name varchar(50),
file_path varchar(300)
)

/* Dynamic table to hold all the scripting lines created.
This table is cleared after the data files are processed.
Steps: 1,change file location, 2, take db offline, 3, copy files, 4,database online
*/

declare @scripts table
(
step int,
scriptText varchar(300)
)

/* Dynamic table to store the changes that are to be made.
This data can be pasted into an email to inform customer of the changes
*/

declare @summary table
(
actions varchar(30),
dbname varchar(50),
file_type varchar(10),
db_file_name varchar(50),
old_path varchar(300),
new_path varchar(300)
)
/* Variable Decleration Area */
declare @id int; --id is created on temp table to use in cursor for processing data
declare @old_file_location varchar(1000) --temporarily stores the old file location for comparisons
declare @tmpString varchar(1000) -- used to temporarily hold strings
declare @temp_dbname varchar(100) -- holds temp database name
declare @temp_virtual_file_name varchar(100) -- holds temp virtual file name
declare @temp_file_type varchar(10) -- holds temp file type either ROWS or LOG
declare @temp_file_name varchar(100) -- holds temp file name
declare @temp_old_file_location varchar(1000) --holds old location of the file

/* get required data and place into @info dynamic table */
insert into @info
select mf.database_id,
db.name as 'dbname',
mf.name as 'virtual_file_name',
mf.type_desc,
REVERSE(SUBSTRING(REVERSE(mf.physical_name), 0, CHARINDEX('\', REVERSE(mf.physical_name)))) as 'db_file_name',
LOWER(SUBSTRING(mf.physical_name, 0, LEN(mf.physical_name) - CHARINDEX('\', REVERSE(mf.physical_name)) + 1)) as 'file_path'

from sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
where mf.database_id NOT IN
(Select database_id from sys.databases
where sys.databases.name in ('tempdb','master','msdb','model','distribution'))
/* Create the scripted commands for the data files */
PRINT('MOVE DATA FILES');

/* Create a cursor on @info table where type_desc is ROWS */
declare c cursor for select id from @info where type_desc = 'ROWS'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin

/* Assign required values into variables from @info db */
select @old_file_location = i.file_path,
@temp_virtual_file_name = i.virtual_file_name,
@temp_file_name = i.db_file_name,
@temp_dbname = i.dbname,
@temp_file_type = type_desc,
@temp_old_file_location = i.file_path,
from @info i
where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_data_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_data_file_path + '\' + @temp_file_name+ ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_data_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE DATA FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
fetch next from c into @id
End

/* Close and deallocate the cursor so we can use it again for dealing with log files */
close c
deallocate c

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Erase the @scripts table prior to adding log file scripts */
delete from @scripts where scriptText <> '';
/* Create the scripted commands for the log files */

PRINT('MOVE LOG FILES')
/* Create a cursor on @info table where type_desc is LOG */
declare c cursor for select id from @info where type_desc = 'LOG'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin
/* Assign required values into variables from @info db */
select @old_file_location = i.file_path, @temp_virtual_file_name = i.virtual_file_name, @temp_file_name = i.db_file_name, @temp_dbname = i.dbname, @temp_file_type = type_desc, @temp_old_file_location = i.file_path from @info i where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_log_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_log_file_path + '\' + @temp_file_name + ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_log_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE LOG FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)

end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)
end

fetch next from c into @id
End

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Print the summary */
PRINT('SUMMARY')
select * from @summary
/* cleanup */
close c
deallocate c





SQL Server ku5zi-bj.mdf aka The Resource Database

9 07 2014

Starting in SQL Server 2005 Microsoft introduced a hidden database called the Resource Database. The resource database contains all the system objects included with SQL Server.

The addition of the resource database was done to expedite the upgrade process, as opposed to adding, modifying, or removing system objects during the upgrade a new resource database is simply copied in and replaces the original resource database.

The objects in the resource database are made available through logical links in the sys schema of all databases.  I must point out that from experience, performing, extreme security hardening, not all of the objects are available to be seen by the mere mortal.

Want a copy of the resource database to play around with?

The easiest way to accomplish this is to grab it from the installation media, although it may be out of date if you have applied any service packs or updates.

I found the file ku5zi-bj.mdf buried deep with in the installation media folder structure. On SQL Server 2014 it is located here x86\Setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn.

Simply copy the database file ku5zi-bj.mdf to where ever you may be keeping your data.

Attach the database through SSMS and be sure to update the .mdf location, remove the log file, and change the name of the database to something else using the AttachAs option.

 





TraceID to Audit Group Map

28 06 2014

I will update this and make it pretty when I get more time but here is the information for those who need or want it regarding the Trace ID’s and their correlation to SQL Auditing groups

 

Trace ID Audit Equivelant SQL Auditing Group
109 Audit Add DB User Event DATABASE_PRINCIPAL_CHANGE_GROUP
108 Audit Add Login to Server Role Event SERVER_ROLE_MEMBER_CHANGE_GROUP
110 Audit Add Member to DB Role Event DATABASE_ROLE_MEMBER_CHANGE_GROUP
111 Audit Add Role Event DATABASE_PRINCIPAL_CHANGE_GROUP
104 Audit Addlogin Event SERVER_PRINCIPAL_CHANGE_GROUP
112 Audit App Role Change Password Event APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
115 Audit Backup/Restore Event BACKUP_RESTORE_GROUP
117 Audit Change Audit Event AUDIT_CHANGE_GROUP
152 Audit Change Database Owner DATABASE_OWNERSHIP_CHANGE_GROUP
128 Audit Database Management Event DATABASE_CHANGE_GROUP
172 Audit Database Object GDR Event DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
129 Audit Database Object Management Event DATABASE_OBJECT_CHANGE_GROUP
135 Audit Database Object Take Ownership Event DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
178 Audit Database Operation Event DATABASE_OPERATION_GROUP
133 Audit Database Principal Impersonation Event DATABASE_PRINCIPAL_IMPERSONATION_GROUP
130 Audit Database Principal Management Event DATABASE_PRINCIPAL_CHANGE_GROUP
102 Audit Database Scope GDR Event DATABASE_PERMISSION_CHANGE_GROUP
116 Audit DBCC Event DBCC_GROUP
14 Audit Login SUCCESSFUL_LOGIN_GROUP
107 Audit Login Change Password Event LOGIN_CHANGE_PASSWORD_GROUP
106 Audit Login Change Property Event SERVER_PRINCIPAL_CHANGE_GROUP
20 Audit Login Failed FAILED_LOGIN_GROUP
105 Audit Login GDR Event SERVER_PRINCIPAL_CHANGE_GROUP
15 Audit Logout LOGOUT_GROUP
118 Audit Object Derived Permission Event SCHEMA_OBJECT_CHANGE_GROUP
103 Audit Schema Object GDR Event SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
131 Audit Schema Object Management Event SCHEMA_OBJECT_CHANGE_GROUP
153 Audit Schema Object Take Ownership Event SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
175 Audit Server Alter Trace Event T RACE_CHANGE_GROUP
171 Audit Server Object GDR Event SERVER_OBJECT_PERMISSION_CHANGE_GROUP
176 Audit Server Object Management Event SERVER_OBJECT_CHANGE_GROUP
134 Audit Server Object Take Ownership Event SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
173 Audit Server Operation Event SERVER_OPERATION_GROUP
132 Audit Server Principal Impersonation Event SERVER_PRINCIPAL_IMPERSONATION_GROUP
177 Audit Server Principal Management Event SERVER_PRINCIPAL_CHANGE_GROUP
170 Audit Server Scope GDR Event SERVER_PERMISSION_CHANGE_GROUP
18 Audit Server Starts And Stops SERVER_STATE_CHANGE_GROUP
113 Audit Statement Permission Event SCHEMA_OBJECT_CHANGE_GROUP