SQL server day-to-day tasks

Transaction log usage %

2011-09-18T21:42:00.000+03:00

To find the transaction log usage of all databases run:

DBCC SQLPERF (Logspace);

GO

DBCC DBINFO

2011-08-29T19:38:00.000+03:00

Another undocumented command:

USE [DataBase]
DBCC DBINFO WITH TABLERESULTS

Gets the db information about current database.

Script all replication procedures for a publication

2011-08-18T01:35:00.000+03:00

In order to script replication procedures (sp_msdel_xxxxx, sp_msins_xxxxx ,sp_msupd_xxxxx) for all articles in a publication run:

use [publicationdb]
go
sp_scriptpublicationcustomprocs [ @publication = ] 'publication_name'

This can be used when stored procs on the subscriber are incorrect, lost, altered (by mistake) or just for reference.

Ref: msdn

How to completely remove replication from a database

2011-08-18T01:19:00.001+03:00

In order to clear everything related to replication on a database you can run:

sp_removedbreplication [ [ @dbname = ] 'dbname' ]

Be careful this can severely 'damage' replication. It will clear all replication objects in specified database.

Ref: msdn

Determine which spid is the head of the blocking chain (find the top blocking SPID and SQL text)

2011-06-03T02:26:00.000+03:00

Simple script to determine which SPID is the head of the blocking chain (find the top blocking SPID and SQL text)

IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT
sp.spid, sp.[status], sp.loginame,
sp.hostname, sp.[program_name],
sp.blocked, sp.open_tran,
dbname=db_name(sp.[dbid]), sp.cmd,
sp.waittype, sp.waittime, sp.last_batch, st.[text]
FROM master.dbo.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.[sql_handle]) st
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
ELSE
SELECT 'No blocking processes found!'

Read SQL server errorlog

2011-04-20T01:08:00.001+03:00

Very handy undocumented stored proc to read SQL server logs:

sp_readerrorlog [logfile number (0-current, 1- previous... )], [1 for errorlog or 2 for agent log], [search word1], [search word 2]

ex1: to display current errorlog:
sp_readerrorlog

ex2: to search current errorlog for word 'restore' or 'log'

sp_readerrorlog 0,1,'restore','log'

The output can be easily redirected to a table like in this article.

Great Free e-book "Dissecting SQL Server Execution Plans"

2010-11-08T13:29:00.000+02:00

Great Free e-book "Dissecting SQL Server Execution Plans".

http://downloads.red-gate.com/ebooks/HighPerformanceSQL_ebook.zip

some great articles regarding data compression

2010-10-20T00:50:00.000+03:00

I would like to share some great articles about data compression:

Data Compression: Strategy, Capacity Planning and Best Practices

Creating Compressed Tables and Indexes

Implementing Data Compression in SQL Server 2008

Data Compression Using the SQL Server Vardecimal Storage Format

Unicode Compression in SQL Server 2008 R2

sp_estimate_data_compression_savings

Impact of Data Compression on Backup Compression with SQL Server 2008

Moving table to different filegroup

2010-09-15T14:26:00.000+03:00

Two useful articles describing table move from one filegroup to another:

http://decipherinfosys.wordpress.com/2007/08/14/moving-tables-to-a-different-filegroup-in-sql-2005/

http://www.mssqltips.com/tip.asp?tip=1112

Microsoft Support Lifecycle of SQL server

2010-09-07T10:25:00.001+03:00

Two helpful links to understand the lifecycle of SQL server:

http://blogs.msdn.com/b/psssql/archive/2010/02/17/mainstream-vs-extended-support-and-sql-server-2005-sp4-can-someone-explain-all-of-this.aspx

http://support.microsoft.com/lifecycle/?LN=en-us&p1=2855&x=13&y=14

Unable to connect to a clustered instance with SSMS

2010-08-18T13:13:00.000+03:00

I had an issue connecting to a clustered instance using <networkname>\<instancename>.
SQL instance was working fine according to errorlog.
The issue was fixed restarting SQL Server Browser.

Property Owner is not available for Database

2010-08-05T10:38:00.000+03:00

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)
------------------------------

Property Owner is not available for Database '[MyDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Cause:

If database owner does not exist in SQL (removed login) this error appear
to check the current owner:
EXEC sp_helpdb MyDB

Solution:

USE MyDB
EXEC sp_changedbowner 'sa'

Migrating VLDB with Log Shipping

2010-08-02T11:49:00.000+03:00

Interesting article on Migrating Very Large Databases with Log Shipping.

http://www.mssqltips.com/tip.asp?tip=2073

How to set max server memory

2010-07-28T18:30:00.000+03:00

Simple method to calculate max server memory:

http://blogs.msdn.com/b/boduff/archive/2008/03/31/how-to-set-max-server-memory.aspx

Disaster recovery: backing up the tail of the log

2010-07-26T15:46:00.000+03:00

Disaster recovery: backing up the tail of the log by Paul Randal

http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx

FIX: VMware ESX and VirtualCenter - High CPU and Memory Usage After Upgrade to VirtualCenter 2.5 and SQL Server

2010-07-23T14:48:00.002+03:00

There was a performance issue on one of the SQL servers used by VMWare Virtual Center.
Problem was high cpu utilization, high IO activity, failed jobs and overall performance degradation.
The solution is described in this article and solves the problem completely.

Direct links:
http://h20000.www2.hp.com/bizsupport/TechSupport/Document.jsp?lang=en&cc=us&taskId=110&prodSeriesId=420496&prodTypeId=18964&objectID=c01433023

Also described as vmware kb:
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004078

FIX: SQL Server orphaned users

2010-07-21T12:50:00.000+03:00

To fix orphaned users you need only 3 commands:

To list all orphaned users:
USE <database>
EXEC sp_change_users_login 'Report'

То fix/ link login to user:
USE <database>
EXEC sp_change_users_login 'Auto_Fix', 'user'

To create new login and link it to a user:
USE <database>
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

More info:
http://support.microsoft.com/kb/274188
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm

Script to check backup history

2010-07-14T10:19:00.001+03:00

Check backup history script:
This script is very helpful when you need to find last backups, physical device, backup size for a database. Currently it is set to retrieve all backups from last 7 days. It can be changed in the where clause.

This script was taken from this article. Link contains other useful scripts for backup history.

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
--http://www.mssqltips.com/tip.asp?tip=1601
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

return random rows from a table

2010-07-02T21:01:00.001+03:00

Hello,
here is one handy command to generate sample data:

SELECT * FROM table_name TABLESAMPLE SYSTEM (10000 ROWS)

this will return 10 000 randomly chosen rows from the table (table_name)

SELECT * FROM table_name TABLESAMPLE SYSTEM (10 PERCENT)

this will return randomly 10% of the rows in the table

FIX: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

2010-07-02T13:53:00.000+03:00

When trying to import data from Excel 2007 file using import wizard in management studio 2008 an error occurred:

TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
------------------------------
BUTTONS:
OK
------------------------------

this was solved installing "2007 Office System Driver: Data Connectivity Components" which can be found here

Enable C2 auditing

2010-06-22T11:22:00.002+03:00

I had a request to enable c2 auditing on a server.
Here is described what is c2 auditing and how to be enabled/ disabled.

In nutshell:

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE

To enable the feature, set 'c2 audit mode' to 1 using the following command:
sp_configure 'c2 audit mode', 1
go

To disable the feature, set 'c2 audit mode' to 0 using the following command:
sp_configure 'c2 audit mode', 0
go

Please note that it will start to create 200 MB files in the default database directory and if there is not enough space SQL Server will be stopped.

SQL Server Trace Flags

2010-06-03T18:19:00.000+03:00

SQL Server Trace Flags can be found here

Attach a database without log file(s)

2010-06-03T14:25:00.006+03:00

This article describes how to attach a database without log file(s) or recover suspect database with damaged log file(s).

1. In SQL 2000, 2005 and above this can be done via GUI:
1.1) remove (or rename) original log file and attach the database
1.2) accept the creation of new log file ( in SQL 2000) or remove the log file from dialog (in SQL 2005 and above)
1.3) database is attached and new log file is created.

2. If 1st step is not applicable you can try to attach via next scripts taken from here:

2.1) Method 1:
USE [master]
GO
EXEC sp_attach_single_file_db @dbname='TestDb',@physname=N'C:\MSSQL\DATA\TestDb.mdf'
GO

2.2) Method 2:

USE [master]
GO
CREATE DATABASE TestDb
ON (FILENAME = N'C:\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

2.3) Method 3:
USE [master]
GO
CREATE DATABASE TestDb
ON (FILENAME = N'\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

However these scripts doesn't work in case of multiple log files (tested on SQL 2000).

3) In case of multiple log files or for some reason above procedures are not helping use next procedure (used to recover from broken ldf or attach without ldf file) taken from a post here:

3.1) detach suspected database (if it is attached) and rename the .mdf to something like .mdf_old and .ldf to .ldf_old
3.2) create a new database of the same name and log file and location as the old database and log file
3.3) stop sql
3.4) rename the new database.mdf or delete it if you don't have enough space - do not touch the .ldf
3.5) move back in the old database.mdf file or rename it back again
3.6) start sql our database should be suspect
3.7) from a query window execute:

use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

3.8) Then set the status of the DB that is giving you the problem (XXXXX) into Emergency Mode by running the following query:

update sysdatabases set status = 32768 where name = 'DBName'
go
checkpoint
go
shutdown with nowait
go

3.9) Go into the data directory (MSSQL\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP.

3.10) Exit the query window.

3.11) Then start up SQL Server from a DOS command window by:

sqlservr -c -T3608 -T4022

note if it is a named instance you should use -s instancename switch

3.12) Bring up another query window and verify that the DB is in emergency mode by:

select Name, Status from Sysdatabases where name = 'DB_Name'

3.13) Verify that the status is 32768. If it is, then issue the query:

dbcc traceon(3604)
dbcc rebuild_log ('DB_Name','log_filename') <--- You will need the quotation marks

REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed

3.14) Take the database out of bypass recovery mode by:

update sysdatabases set status = 0 where name = 'DBName'

3.15) Exit the query window and then shutdown (Ctrl-C in the DOS window) and restart SQL server. Verify the status of the database by running:

DBCC NEWALLOC

DBCC CHECKDB

This procedure was tested on SQL 2000 and works perfect.

24 hours of PASS started!!!

2010-05-19T18:39:00.002+03:00

http://www.sqlpass.org/24hours/2010/Sessions.aspx#DBA

Here is the link to recordings of all sessions:
http://www.sqlpass.org/LearningCenter/24Hours.aspx

Disaster Recovery SQL 2000 step by step

2010-04-29T14:21:00.002+03:00

Today I had to bring online damaged (hardware malfunction) SQL 2000.

We have only database bak and trn files on tape.

Here are the steps used (default instance):

1) Install OS windows 2000
2) Install all OS patches, SP etc..
3) Install SQL 2000
4) Install all SQL SP, hot fixes and patches as before the disaster
5) Restore Master Database:
First you need to start SQL in single user mode to do this

5.1) Stop MSSQL

net stop MSSQLServer

5.2) And start it with -'m' note that if path is not set you should do this from C:\Program Files\Microsoft SQL Server\MSSQL\BINN

sqlservr -m

5.3) Start osql and restore database

osql -E
1> restore database master from disk ='c:\temp\MasterBackup.bak'
2> go

5.4) After restore is complete SQL is stopped and you need to start it

net start MSSQLServer

6) Connect with Enterprise Manager and restore msdb and model (as a normal databases).
7) Restart SQL.
8) All user databases are now suspect (files are missing)
9) bak and trn files were restored from tape to original location (where maintenance plan was configured to store them)
10) There were more than 10 trn log files ( backup schedule: (1 full + 24 trn log backups) /day) to be restored it was easier to use Enterprise manager (we have msdb restored) to restore last full backup of each database + all transaction log backups.

Done.

How to transfer logins and passwords between instances of SQL Server

2010-04-15T16:50:00.000+03:00

Article "How to transfer logins and passwords between instances of SQL Server" can be found:

http://support.microsoft.com/kb/246133

SQL Server versions database

2010-04-14T17:34:00.001+03:00

This is very helpful site containing SQL versions.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

FIX: SQL server management studio "no description found"

2010-04-13T14:55:00.001+03:00

An error occurred when saving a maintenance plan in management studio:

"no description found"

A quick fix found here was very helpful.
Just run in command prompt:

Regsvr32 msxml3.dll
Regsvr32 msxml6.dll

And restart management studio...

FIX: When creating new maintenance plan an error occurred: Could not load file or assembly 'DdsShapesLib'

2010-04-12T11:45:00.000+03:00

An error occurred when creating new maintenance plan:

Could not load file or assembly 'DdsShapesLib, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.DataTransformationServices.Design)

Fix in nutshell:
First you need to find where DdsShapesLib.dll is.
C:\>dir gacutil.exe /s

Go to that directory

Then start following command:
C:\xxx\xxx\>gacutil.exe /i DdsShapesLib.dll

(it is possible gacutil.exe is not in the PATH and cannot be started you can add it or to start the toll with whole path c:\windows\xxx\xxx\gacutil.exe):

And you will get the following output:

Microsoft (R) .NET Global Assembly Cache Utility. Version 1.1.4322.573
Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.

Assembly successfully added to the cache

Restart management studio and now you are able to create maintenance plans.

Detailed explanation and fix can be found:

http://msdn.microsoft.com/en-us/library/aa337405(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ex0ss12c(VS.80).aspx

FIX: Drop failed for Job 'xxxxxxxx'. (Microsoft.SqlServer.Smo)

2010-04-12T10:24:00.002+03:00

Today I had issue deleting an old job related to a missing maintenance plan.

The solution was found in this post.

Database Mirroring for Non Domain MS SQL Servers

2010-04-09T14:36:00.002+03:00

A very helpful post on this topic:

http://danmacs.blogspot.com/2009/05/database-mirroring-for-non-domain-ms.html

System tables reference

2010-04-01T00:00:00.000+03:00

Very handy system tables reference:

System Tables (Transact-SQL) reference.
http://msdn.microsoft.com/en-us/library/ms179932(v=SQL.100).aspx

FIX: Log shipping failed due to broken/ damaged tansaction log backup.

2010-03-10T13:22:00.003+02:00

Log shipping has failed due to incomplete/ damaged transaction log backup.
This issue occurred also when there is lack of free space on drive where backups are placed???(no logical explanation).

For some reason transaction log backup was saved with errors (damaged) and then transfered to the destination server.
Result is - log shipping has failed.

I've prepared a 6 step guide how to repair log shipping:

Create Full Backup of source database (mine was placed '\\sharedstorage\XYZ\XYZ_backup.bak')
Disable Restore and copy jobs on destination server.
Restore database with:

restore database XYZ from disk ='\\sharedstorage\XYZ\XYZ_backup.bak'
with replace, norecovery;

If destination database should be in standby mode go to step 5; else go to step 6

Restore next transaction log backup and set database to standby (transaction log backups are working on source):

restore log XYZ from disk ='\\sharedstorage\XYZ\XYZ_.trn'
with standby= 'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\UNDO_standby_file.BAK'

*note if full backup takes too long or is old and we are trying to restore transaction log backup which is not the correct one error will be generated. If backup is older error will be transaction log xxxx is to early. And if backup is newer the error will be transaction log is too recent.

Enable Restore and copy jobs on destination server.
Done.

Aditional links:

http://msdn.microsoft.com/en-us/library/ms175106.aspx
http://msdn.microsoft.com/en-us/library/ms188625.aspx

FIX: Disaster Recovery: What to do when the SA account password is lost and no admin account present in SQL Server 2005

2010-03-09T14:30:00.002+02:00

Another very helpful post when you are locked out and you do not have administrative access to SQL:
http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

2010-03-09T14:17:00.000+02:00

Very helpful article on this topic http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

FIX: After installing SSIS an erorr is generated Microsoft.SqlServer.DTSRuntimeWrap when creating a maintenance task

2010-03-02T11:29:00.000+02:00

On 64 bit Server 2003 with Sql 2005 64-bit after installing SSIS an erorr is generated 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap) when creating a maintenance task.

Fix:

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll"

or

regsvr32 "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll"

The solution was found in MSDN's forums here.

FIX: SQL server 2008 failover cluster 'RequireKerberos' error

2010-02-25T18:39:00.001+02:00

Today I had troubles installing SQL 2008 fail over cluster on Windows 2008.

Installation failed with error:

"The following error has occurred: There was an error setting private property 'RequireKerberos' to value '1' for resource '%1'. Error: Value does not fall within the expected range."

The fix for this was found in that post:
http://www.sqlservercentral.com/Forums/Topic723728-391-1.aspx?Update=1

Using slipstream method:
http://blogs.msdn.com/petersad/archive/2009/03/02/sql-server-2008-basic-slipstream-steps.aspx

Before that I had to remove half installed instance steps are described here:
http://msdn.microsoft.com/en-us/library/ms191545.aspx#Remove

Steps in brief:

Download SQL 2008 SP1 from here and extract it (it was used C:\SP1)
SQLServer2008SP1-KB968369-x64-ENU.exe /x:C:\SP1
go to C:\SP1
dir sqlsupp* /s and start the .msi and install the support file for slipstream of SQL 2008
then start the installation from the SQL 2008 source media:
SETUP.EXE /PCUSource=C:\SP1

These steps fixed the issue and cluster was installed successfully.

Truly Level 400 Performance Monitoring and Tuning

2010-02-22T11:16:00.000+02:00

Very helpful web cast.

http://sqlworkshops.com/

Property Owner is not available for Database '[DBName]'.

2010-01-25T16:12:00.001+02:00

When trying to see the properties of a database an error is raised:
Property Owner is not available for Database '[DBName]'.

Cause:
This error appears because the owner of the database is no longer available (deleted/ removed).

Solution:
Change the owner of the database.

Example:
USE DBName
EXEC sp_changedbowner 'sa'

Source: http://www.sqlcoffee.com/Troubleshooting011.htm

FIX: SQL Server 2005 Full Text Search services on Windows 2008 cluster failed to Start Up

2010-01-22T12:47:00.001+02:00

Problem:
On newly installed SQL 2005 Cluster Full text search resource failed to start.

Solution:
In nutshell- Install SQL 2005 SP2

Detailed information can be found in Microsoft's article Problem #3:

http://support.microsoft.com/kb/936302

If SP2 doesn't fix the issue check the link below:

http://sqllearnings.blogspot.com/2009/05/sql-server-2005-full-text-search.html

Disk Partition Alignment Best Practices for SQL Server

2010-01-20T10:55:00.003+02:00

Here is one very helpful article for Disk Partition Alignment:

http://msdn.microsoft.com/en-us/library/dd758814.aspx

In brief:

Diskpart
list disk
select disk <disknumber>
create partition primary align= <offset_in_kb>
assign letter=<driveletter>
format fs=ntfs unit=64K label="<label>" nowait

<offset_in_kb> should be 1024

Unable to install SQL 2005 of CPU cores is not a power of 2

2010-01-14T10:56:00.003+02:00

On a computer that has a multicore processor, if the ratio of logical processors to physical sockets is not a power of 2, you cannot install SQL Server 2005

Solution:

http://support.microsoft.com/kb/954835/en-us

Fix error (1807): Could not obtain exclusive lock on database 'MODEL'

2010-01-12T17:58:00.001+02:00

Occurrence:
When creating a database an error is raised:

Server: Msg 1807, Level 16, State 2, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.

There is a quick fix for this:
Kill all connections to database 'Model'.

A handy stored procedure returning general information for SQL server

2010-01-11T15:57:00.000+02:00

This SP returns general information for SQL server.

EXEC xp_msver

Similar output can be produced by this script:

SELECT
SERVERPROPERTY ('ServerName') AS ServerName,
SERVERPROPERTY ('Edition') AS Edition,
SERVERPROPERTY ('EngineEdition') AS EngineEdition,
SERVERPROPERTY ('ProductLevel') AS ProductLevel

FIX: [LOG] Unable to read local eventlog (reason: The parameter is incorrect)

2010-01-11T10:31:00.003+02:00

How to fix on SQL 2005 SP1:
"[LOG] Unable to read local eventlog (reason: The parameter is incorrect)."

There are many posts regarding this issue.
However installing SP3 is the key.

Problems running sp_helpdb in SQL Server

2009-12-07T16:23:00.001+02:00

There is a complete how-to fix this:

http://www.mssqltips.com/tip.asp?tip=1217

negative unallocated space using sp_spaceused

2009-12-07T16:21:00.000+02:00

negative unallocated space using sp_spaceused can be fixed with:

EXEC SP_SPACEUSED @UPDATEUSAGE='TRUE'

or

DBCC UPDATEUSAGE ('Database')

MDX error when deleting SQL job

2009-11-30T13:05:00.002+02:00

MDX error is raised when trying to delete a job. This is because originating_server is not the name of current server.

This can be fixed with:

UPDATE msdb.dbo.sysjobs SET originating_server = 'current_server' WHERE
originating_server <> 'current_server'

This will modify SYSTEM Database MSDB and it is highly recommended to create a backup before executing the script.

sp_helptext

2009-11-24T11:06:00.002+02:00

A handy stored procedure which:
"Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view."
It is creating a create script.

Usage:

USE master;
EXEC sp_helptext 'syslogins';
--returns create view statement for syslogins.

USE master;
EXEC sp_helptext 'sp_MSforeachtable';
--returns create proc statement for sp_MSforeachtable

User permissions in a database/instance

2009-11-19T12:14:00.006+02:00

Four stored procedures and one view to check user rights/ permissions over database/ instance:

sp_helpuser
Reports information about database-level principals in the current database.

sp_helprolemember
Returns information about the members of a role in the current database.

sp_helpsrvrolemember
Returns information about the members of a SQL Server fixed server role.

select * from sys.database_principals
Returns a row for each principal in a database.

sp_helprotect
Returns a report that has information about user permissions for an object, or statement permissions, in the current database

A great article related to this topic:here

when you lockout your account setting your default database offline, single user or detach it

2009-11-13T15:26:00.002+02:00

when you lockout your account setting your default database offline , single user mode or detach it

You can connect to other database using sqlcmd:

sqlcmd -E -d master

and then to on- line or set multi user the default database

alter database [dbname] set online

or

alter database [dbname] set multi_user

A great article about this issue can be found here

Re cycle MSSQL errorlog

2009-11-13T15:02:00.002+02:00

Two ways to cycle an errorlog:

DBCC errorlog

or

EXEC sp_cycle_errorlog;

There is also a stored procedure for SQL agent errorlog:

EXEC dbo.sp_cycle_agent_errorlog

PWD in MS SQL

2009-10-26T01:09:00.002+02:00

Just a select that shows which database is currently used (as pwd for FS) :

select db_name();

first post

2008-01-08T00:26:00.000+02:00

Hello, this is my first post.