Tips and trix when troubleshooting databases.
EPiServer CMS database - Tips and trix for the EPiServer CMS database
Update statistics
The first thing to do when troubleshooting a database is to update statistics
SQL 2008
In SQL Manager execute the stored procedure
sp_updatestats
SQL 2008
Troubleshooting a database deadlock with SQL-profilerIsolation level for current session
select case transaction_isolation_level
when 1 then 'Read Uncomitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable Read'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else 'Unspecified'
end
from sys.dm_exec_sessions
where session_id = @@spid
SQL script to list all integer columns greater than 1’000’000
There can be a major problem if an integer counter gets higher than 2’100’000’000 sense it’s the max value of it.
DECLARE @t nvarchar(MAX)
DECLARE @c nvarchar(MAX)
DECLARE @Query NVARCHAR(MAX)
DECLARE tc CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'int';
OPEN tc
FETCH NEXT FROM tc INTO @t, @c
WHILE (@@fetch_status <> -1)
BEGIN
SET @query = 'SELECT ''' + @t + '.' + @c
+ ''', * FROM (SELECT MAX(' + @c + ') as m FROM ' + @t
+ ') t WHERE t.m > 1000000' ;
EXEC sp_executesql @Query
FETCH NEXT FROM tc INTO @t, @c
END
CLOSE tc
DEALLOCATE tc
SQL 2008 Reset password
Log in as local administrator for the machine running the SQL server
Open SQL Server Configuration Manager
Right click on the server instance you want to change password for and select “Properties”
On the advanced tab add “-m;” to the property “Start Parameters”
Restart the SQL server instance
Open SQL Server Managememt studio, and log in with “Windows Authentication”
Create a new user with “sysadmin” server role selected
Remove the “-m;” from the “Start Parameters”
Restart the server
Open SQL Server Managememt studio, and log in with the new user