Retrieving Log Space Information within a SQL Server Instance - The Stored Procedure!

In yesterday's post, we saw how we can retrieve log space information for all the databases within a SQL Server instance using the command DBCC sqlperf(logspace).

Today we will refine the process even more by creating a stored procedure that returns log space information for a given database!

--******************************
--CREATING THE STORED PROCEDURE
--******************************

--Select the database in which the stored
--procedure will be created
USE [spDBName]
GO


--Check if the stored procedure exists
--and if so drop it
IF OBJECT_ID('spDBLogInfo') IS NOT NULL
DROP PROCEDURE spDBLogInfo
GO


--Create the stored procedure.
--The only input parameter will be the database name.
CREATE PROCEDURE spDBLogInfo
@DBname NVARCHAR (250)
AS
BEGIN
SET nocount ON;


--
-- Main logic
--

--Step 1: Create temporary table
CREATE TABLE #temptbl
(
DBName NVARCHAR(250),
logsize FLOAT,
logspaceused FLOAT,
status INT
)


--Step 2: Populate temporary table with log size information
INSERT INTO #temptbl
EXEC('DBCC sqlperf(logspace)')


--Step 3: Process the temporary table
SELECT DBName,
ROUND(logsize,2) as 'Log Size (MB)',
ROUND(logspaceused,2) as 'Log Space Used (%)',
ROUND((logsize-(logsize*(logspaceused/100))),2) as 'Available Log Space (MB)'
FROM #temptbl
WHERE dbname=@DBname
END
GO
-------------------------------

Now, let's say we want to see log space information about the database 'temp'.
The only we need to do is this:

EXEC spDBName..spDBLogInfo 'temp'

...and this is what we get:

Feel free to use the stored procedure for your database administration needs!

Cheers!
Read more on this article...

Retrieving Log Space Information within a SQL Server Instance

In the everyday life of a Database Administrator there is the task of maintaining the database logs in terms of size they occupy on the disk. Of course, there are automated maintenance and reporting procedures for this task as well as for many other tasks but it is not few the cases where the DBA needs to manually maintain a SQL Server instance!

The fastest way to get log space information for all the databases under a SQL Server instance is to use the following T-SQL command:

DBCC SQLPERF(LOGSPACE)

The above command returns a record for each database under the current SQL Server instance which contains the following information:
- Database Name
- Log Size (MB)
- log Space Used (%)
- Status

Additionally, if you like to process this information, you can do so by first storing it into a temporary table.
You can do this as follows:

--Step 1: If temporary table exists, then drop it
IF Object_id(N'tempdb..#tempTbl') IS NOT NULL
DROP TABLE #temptbl


--Step 2: Create temporary table
CREATE TABLE #temptbl
( dbname VARCHAR(250),
logsize FLOAT,
logspaceused FLOAT,
status INT
)


--Step 3: Populate temporary table with log size information
INSERT INTO #tempTbl
EXEC('DBCC SQLPERF(LOGSPACE)')


--Step 4: Process the temporary table
--Examples:
SELECT *
FROM #tempTbl
ORDER BY logsize DESC


SELECT *
FROM #tempTbl
ORDER BY logspaceused ASC

I hope you enjoyed the post as much as I did writing it! :)
Read more on this article...

How to retrieve size information for all the tables in a SQL Server Database

In an older post, I described how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure "sp_MSforeachtable".

Another common task is when you want to retrieve size information for all the tables in a database.

Again, by using "sp_MSforeachtable", you can easily do that in three simple steps:

--Step 1:
--Create temporaty table for the session
create table #tblInfo(
[name] nvarchar (255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
);


--Step 2:
--Using the stored procedure sp_spaceused retrieve
--the size information for all tables and store it in the temporary table
EXEC sp_MSforeachtable @command1="INSERT #tblInfo EXEC sp_spaceused '?'";


--Step 3:
--Access the results
select * from #tblInfo;

Note: Because sp_spaceused returns the size information as a string (except the number of rows), you will have to manipulate the data in the temporary table using casting prior to run sorting operations etc.

Examples:

--Sort the results by unused space (descending)
select * from #tblInfo
order by cast(substring(unused,0,charindex(' ',unused)) as int) desc;


--Sort the results by reserved space (descending)
select * from #tblInfo
order by cast(substring(reserved,0,charindex(' ',reserved)) as int) desc;

Additionally, you can create another temporary table which can contain the converted values (i.e. in MB instead of KB) of the first table.

For examnple:

select [name],
rows,
cast(substring(reserved,0,charindex(' ',reserved)) as int)/1024 as reserved_in_MB,
cast(substring(data,0,charindex(' ',data)) as int)/1024 as data_in_MB,
cast(substring(index_size,0,charindex(' ',index_size)) as int)/1024 as index_size_in_MB,
cast(substring(unused,0,charindex(' ',unused)) as int)/1024 as unused_in_MB
into #tblInfoConverted
from #tblInfo;


--and ... voila!
select *
from #tblInfoConverted
order by reserved_in_MB desc;


Until next time!
Read more on this article...

Listing Directory Contents using T-SQL

In many cases during the development of a sophisticated SQL Server Agent job, you might need to access the file system. For example you might want to list the contents of a specific directory and based on the output to proceed to other actions folllowing a series of steps that belong to a broader logic implemented via a SQL Server Agent job.

Don't worry, there's nothing stopping you from performing the above operation!

For such and similar purposes you can always use the extended stored procedure xp_cmdshell.

As described in SQL Server BoL, xp_cmdshell takes as a parameter a string and passes it to a Windows command shell for execution. Any output is returned as rows of text.

xp_cmdshell, by default, is disabled within a SQL Server instance and there is a good reason for this. When this extended stored procedure is enabled, it introduces a security risk that you should be aware of. As xp_cmdshell allows executing commands on the Operating System's level, you should be extremely careful with who has the right to execute this stored procedure. To this end, you should always use xp_cmd with caution.

OK, enough with the theory. Let's proceed with a practical example and some T-SQL!

--You can enable xp_cmdshell using the following T-SQL statement:
exec sp_configure 'xp_cmdshell',1
GO
RECONFIGURE WITH override
GO


Then, if you want for example to list the contents of the directory "c:\tmp" you can do so by executing the following T-SQL statement:

-- You first create a temporary table for storing the contents of the directory.
CREATE TABLE #dirContents
(
contents NVARCHAR(255)
)
GO


-- You then execute xp_cmdshell by using the DOS "dir" command.
-- You store the output to the temporary table created earlier.
-- The contents are stored as string expressions line-by-line.
-- Each empty line is a NULL.
INSERT INTO #dirContents
EXEC xp_cmdshell 'dir "c:\tmp"';


-- You can then scan the table for the output and analyze the data
-- using string manipulation techniques
SELECT *
FROM #dirContents

If you want to disable xp_cmdshell, you can do so as follows:

exec sp_configure 'xp_cmdshell',0
GO
RECONFIGURE WITH override
GO


I hope you found the post useful!

Cheers!
Read more on this article...

Selected Tweets of Year 2011!

With just a few hours remaining for entering the new year 2012, I could do nothing else than preparing this blog post! In the end of every year, I gather up all my tweets for that year and post it here!

It is a privilege to communicate with the SQL Server Community via all possible channels like blogs, forums, offline and online events, conferences and of course, Twitter!

You can find 2011's tweets below:

Blog Posts/Articles and Announcements
[Blog] Dynamic T-SQL Generator v1.0 is now on CodePlex! - http://bit.ly/rqG5F2 #in #fb #MVPBuzz #SQLServer #DynamicSQLGen

[Blog] Useful SQL Server Knowledge - Set 1 - http://bit.ly/sa3qqv #in #fb #MVPBuzz #SQLServer

[Blog] My #SQLServer utility Snippets Generator goes open source on CodePlex! - http://bit.ly/vtDMC6 #MVPBuzz #in #fb

[Blog] Webcast on #SQLServer Denali RC0 - Poll - http://bit.ly/uzwbHR #in #fb #MVPBuzz

[Blog] Creating an instance of the COM component with CLSID ... http://bit.ly/uACL8X #sqlserver #fb #in #MVPBuzz

[Blog] Retrieving Only the Date from a Datetime Value in #SQLServer - http://bit.ly/piFTjf #in #fb #MVPBuzz

[Blog] Sequence Objects in #SQLServer - http://bit.ly/qRckwS #in #fb #MVPBuzz

[Blog] Using Proxy Accounts in #SQLServer Agent Jobs - http://bit.ly/vX8dkh #in #fb #MVPBuzz

[Blog] Updating #SQLServer Tables Without Causing Blocking - http://bit.ly/umv3ZQ #in #fb #MVPBuzz

[Blog] Backing up a Database in a Network Folder - http://bit.ly/oiWuWJ #MVPBuzz #in #fb #SQLServer

[Blog] Tool that generates dynamic T-SQL! - http://bit.ly/l7jxkm #in #fb #MVPBuzz

[Blog] Executing Heavy Set-Based Operations Against VLDBs in #SQLServer - http://bit.ly/iFHKMR #in #fb #MVPBuzz

[Blog] Using Unicode in #SQLServer - http://bit.ly/lgoLRj #MVPBuzz #in #fb

[Blog] Microsoft MVP for the third year in a row! - http://bit.ly/gmUb0U #in #fb #MVPBuzz #SQLServer

[Blog] Using the NOLOCK Table Hint - http://bit.ly/hqqQ9F #fb #in #MVPBuzz #SQLServer

[Blog] Database [Database_Name] cannot be upgraded because it is read-only or has read-only files - http://bit.ly/ftJhw8 #in #MVPBuzz #fb

[Blog] No global profile is configured. Specify a profile name in the @profile_name parameter - http://bit.ly/hdFcHA #MVPBuzz #in #fb

[Blog] Free Utility for SQL Server 2011 CTP1 - Snippets Generator v1.0 - http://bit.ly/hnxImn #in #fb #MVPBuzz #SQLServer

[Blog] Webcast: Introducing SQL Server "Denali" (CTP1) - http://bit.ly/hN9srP #fb #in #MVPBuzz #INETA_EU #CDNUG



Miscellaneous
Generating T-SQL snippets for SQL Denali with ease: Snippets Generator Project - http://bit.ly/rKZFYS #MVPBuzz #SQLSnippetsGen #SQLServer

Preparing my session on #SQLServer Denali CTP 3! - The exciting part is yet to come: The demos!!! #MVPBuzz

On a domain pc/server, in a cmd prompt you can type "echo %logonserver%" to find out which domain controller authenticates you.

I am celebrating 100.000 (+) visits on my blog and I am giving away a utility I developed! The tool is: Dynamic T-SQL Generator! #MVPBuzz

Today my blog has reached 100.000 visits! Thank you for your support!!! - http://bit.ly/PKBi9 #in #fb #MVPBuzz

I am a #Microsoft #SQLServer MVP for a third year in a row! A huge thanks to you, the Community, for supporting my efforts! #in #fb #MVPBuzz

RT @CDNUG: The #Microsoft Cloud Power Event in Cyprus! - http://bit.ly/emiPUh - Save the date: March 10, 2011 #MVPBuzz #fb

[Personal Website Updated] New section added: Webcasts & Screencasts - http://bit.ly/hXPnMZ #in #MVPBuzz

Just finished recording my Webcast on #SQLServer Denali! I will upload it tomorrow! Make sure you check out my blog at: http://bit.ly/PKBi9

A really cool feature! RT @mssqlserver: SEQUENCE in SQL Server 2011 http://bit.ly/gMfC68 #SQLServer

Preparing a webcast for introducing #SQLServer "Denali"! More info soon! #in #fb #MVPBuzz

I really like the T-SQL code snippets enhancement in #SQLServer Denali! #MVPBuzz #in

AdventureWorks Sample Database for #SQLServer "Denali" CTP 1 - http://bit.ly/9hBGub #in #fb

Wishing a Happy New Year 2012 full of health and of course ... SQL Server!
Read more on this article...

Dynamic T-SQL Generator v1.0 is now on CodePlex!

Earlier this year, in one of my blog posts, I presented Dynamic T-SQL Generator: a tool I developed that generates dynamic T-SQL based on a given static T-SQL script and vice versa.

Dynamic T-SQL can be used in many cases when you develop SQL Server routines. An example is embedding T-SQL scripts in SQL Server functions and stored procedures (i.e. sp_send_dbmail).

As i believe that the Technical Community might find the tool useful, I decided to create a relevant open source project on CodePlex.

Feel free to visit my project's page on CodePlex and provide your valuable feedback!

Here's a screenshot of the tool:

Here's the link to Dynamic T-SQL Generator.
Read more on this article...

Useful SQL Server Knowledge - Set 1

A few days ago I started writing an article that it would provide a set of useful tips on SQL Server topics that are met in the everyday life of DBA/Database Developer.

The article is now completed and you can find it below! It is organized in the following categories:

• Solutions to common issues
• Basic string functions
• Performance-related tips
• Maintenance
• Miscellaneous

--
-- A: Solutions to common issues
--

--1: Resolving the "Divide by zero" error (by example)
DECLARE @denominator INT
SET @denominator = 0
SELECT 1 / ISNULL(NULLIF(@denominator, 0), 1)

--2: Handling NULL and empty values
----Step 1: Create the IsEmpty user-defined function
CREATE FUNCTION IsEmpty
(
@input AS VARCHAR(250),
@newValue VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
-- First handle the case where the input value is a NULL
DECLARE @inputFiltered AS VARCHAR(250)
SET @inputFiltered = ISNULL(@input, '')


-- The main logic goes here
RETURN (CASE RTRIM(LTRIM(@inputFiltered)) WHEN '' THEN RTRIM(LTRIM(@newValue)) ELSE RTRIM(LTRIM(@inputFiltered)) END)


END
GO
----Step 2: Usage
SELECT dbo.IsEmpty(@column_to_check, @new_value);

--3: Handling the error "A transport-level error has occurred when sending the request to the server"
If the problem occurs in a SSMS Query Window, just open a new one and run the T-SQL statements again. In a different case, re-initiate the request to the SQL Server instance.

--4: Handling the error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
Change the default language to "us_english" for the given SQL Server login:

USE [master];
ALTER LOGIN "LOGIN_NAME" WITH DEFAULT_LANGUAGE = us_english;

Best practice: Always use the ISO date format in your data applications/T-SQL scripts: YYYY-MM-DD

--5: Handling the error "The multi-part identifier ... could not be bound"
Be careful with the use of subqueries and table aliases. Don't forget to reference the correct table aliases in your T-SQL code.

Also, keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries' tables.

--6: Handling the error "String or binary data would be truncated"
Either use an adequate size for the table columns in which the data is inserted or cast the data by removing redundant characters. I suggest the first approach.

--7: Handling the error "Error converting data type varchar to float"
----Step 1: Create the Varchar2Float user-defined function
CREATE FUNCTION [dbo].[Varchar2Float]
( @inputString VARCHAR(50) )
RETURNS FLOAT
AS
BEGIN
--Prepare the string for casting/conversion
SET @inputString = REPLACE(@inputString, '.', '')
SET @inputString = REPLACE(@inputString, ',', '.')


--Perform the conversion and return the result
RETURN CAST(@inputString AS FLOAT)
END;
----Step 2: Usage
SELECT dbo.Varchar2Float(@value)

--8: Handling the error "Database [Database_Name] cannot be upgraded because it is read-only or has read-only files"
Make sure that the user account on which the SQL Server instance database engine is running has full access to the database files.


--
-- B: Basic string functions
--

--1: Returns @length characters from @expression starting from @start_index
SELECT SUBSTRING(@expression, @start_index, @length)

--2: Finds the given @pattern in the @string and replaces it with the @replacement_string
SELECT REPLACE(@string, @pattern, @replacement_string)

--3: Returns the size of @string in terms of number of characters
SELECT LEN(@string)

--4: Returns the first @num_chars characters of the @string counting from the left
SELECT LEFT(@string, @num_chars)

--5: Returns the first @num_chars characters of the @string counting from the right
SELECT RIGHT(@string, @num_chars)

--6: Removes the leading blank spaces
SELECT LTRIM(@expression)

--7: Removes the trailing blank spaces
SELECT RTRIM(@expression)


--
-- C: Performance-related tips
--

--1: Avoiding locking when reading data (however, dirty reads are allowed)
SELECT [columnName]
FROM [tableName] WITH (NOLOCK)

--2: Rebuilding indexes in SQL Server 2005 or later
----Rebuild a specific index with using parameters
USE [DATABASE_NAME];
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);


----Rebuild all indexes in a table with using parameters
USE [DATABASE_NAME];
ALTER INDEX ALL ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);

--3: Rebuilding all the indexes in a database
----Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)";


----Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";


----Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)";


----Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)";

--4: Updating database tables without causing blocking
UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...


--
-- D: Maintenance
--

--1: Shrinking an entire database
DBCC SHRINKDATABASE([DBName],[PercentageOfFreeSpace]);

--2: Truncating an entire database
DBCC SHRINKDATABASE([DBName],TRUNCATEONLY);

--3: Shrinking a data/log file
USE [DBName];
DBCC SHRINKFILE ([Data_Log_LogicalName],[TargetMBSize]);

--4: Truncating a data/log file
USE [DBName];
DBCC SHRINKFILE ([Data_Log_LogicalName],TRUNCATEONLY);

--5: Renaming a Windows login
ALTER LOGIN "[Domain or Server Name]\[Windows Username]"
WITH NAME="[New Domain or New Server Name]\[Windows Username]";

--6: Renaming a SQL Server login
ALTER LOGIN "[SQL Server Login Name]"
WITH NAME="[New SQL Server Login Name]";

--7: Creating Logins for orphaned SQL Server users
USE [DBName];
EXEC sp_change_users_login 'Auto_Fix', '[UserName]', NULL, '[Password]';

--8: Changing the Database Owner in a SQL Server Database (SQL Login)
USE [DBName];
EXEC sp_changedbowner '[SQL_Login_Name]';

--9: Changing the Database Owner in a SQL Server Database (Windows Login)
USE [DBName];
EXEC sp_changedbowner '[DomainName\UserName]';

--10: Backing up a Database in a Network Folder (creating the destination media)
USE [master];
EXEC sp_addumpdevice 'disk', 'NetworkDeviceName','\\serverName\backupFolder\BackupFileName.bak';
--Now you can backup databases onto the network device created above


--
-- E: Miscellaneous
--

--1: Gets basic information on the current SQL Server instance
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY ('ProductLevel') AS ProductLevel,
SERVERPROPERTY ('Edition') AS Edition,
SERVERPROPERTY('MachineName') AS ServerName,
SERVERPROPERTY('ServerName') AS Server_and_Instance_Names

--2: Gets basic table index information
EXEC sp_helpindex 'schema.table_name'

--3: Connecting to Windows Internal Database (SSEE)
From within SSMS (Express Edition works as well):

• Server Type: Database Engine
• Server Name: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
• Authentication: Windows Authentication

* Make sure that the Named Pipes protocol is enabled!

I hope you found this article useful!

Cheers!
Read more on this article...

Snippets Generator is now on CodePlex!

Early this year, in one of my blog posts, I presented Snippets Generator: a tool I developed that generates snippets for SQL Server Denali.

Snippets can become quite handy when you want to write code fast, without having to repeat/type certain sets of T-SQL statements that are often used.

As the purpose of this blog and generally the purpose of my entire Community activity is to exchange knowledge with fellow Community members and support the technical Community, I decided to take it one step further and publish Snippets Generator as an open source project on CodePlex. CodePlex is Microsoft's open source project hosting web site and there you can find thousands of great open source projects/applications that you can freely use.

Feel free to visit my project's page on CodePlex and provide your valuable feedback!

Here's the link to Snippets Generator.
Read more on this article...

Webcast on SQL Server Denali RC0 - Poll

With SQL Server Denali RC0 released a few weeks ago, I am thinking of recording a webcast with topic its Database Engine features mainly focusing on the programmability area.

To this end, I am conducting a poll in order to see if you are interested in attending such a webcast.

If you are interested you just need to complete the following poll!



When the webcast is available, you will receive an email notification!
Read more on this article...