Wednesday, December 26, 2012

Find available space in a file group on SQL Server part 2

Well, it seems the DBA's frowned upon my access rights and quietly killed them.  My previous post regarding finding the available space on the server no longer works.  This new query though does, though...



-- check to make sure temp tables don't exist
SELECT      
      b.groupname AS 'File Group',
      SUM(CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))) 

          AS [Currently Allocated Space (MB)],
      SUM(CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))) 

          AS [Space Used (MB)],
      SUM(CONVERT (Decimal(15,2), ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))) 

          AS [Available Space (MB)]
     FROM dbo.sysfiles       AS a WITH (NOLOCK)
          JOIN sysfilegroups AS b WITH (NOLOCK) 

             ON a.groupid = b.groupid
     GROUP BY B.groupname

Thursday, November 29, 2012

Check for existing temp table

This code snippet allows you to check whether a temp table current exists.  If so, drop it.  I use this quite frequently when testing code that I'll run numerous times...

-- check to make sure temp tables doesn't exist
IF (OBJECT_ID('tempdb..#Report')   IS NOT NULL)
            drop table #Report


Wednesday, July 25, 2012

Find available space in a file group on SQL Server

Yesterday I attempted to add a new index to my development database and it returned an error stating that the file group didn't have enough disk space.  After a bit of searching I discovered the following code that would show the Total allocated and free MB worth of disk space in each file group.


-- check to make sure temp tables don't exist
IF (OBJECT_ID('tempdb..#fileStats') IS NOT NULL)
            drop table #fileStats

IF (OBJECT_ID('tempdb..#fileGroup') IS NOT NULL)
            drop table #fileGroup


-- Create temporary holding tables
create table #fileStats
    (
        fileID          int,
        fileGroup       int,
        totalExtents    int,
        usedExtents     int,
        name            varchar(255),
        fileName        varchar(1000)
    )

create table #fileGroup
    (
        groupid         int,
        groupname       varchar(256)
    )


-- Insert 
insert into #fileStats
    exec ('DBCC showfilestats with no_infomsgs')

-- Insert
insert into #fileGroup
    select  groupid, groupname
    from sysfilegroups

-- Get resulting set
-- size returned from showfilestats is in Extents -> 1 Extent is 64kb
-- so we need to multiply the sum of TotalExtents by 64 and then divide by 1024 to get
-- the actual MB

select  g.groupname,
        sum(TotalExtents)*64.0/1024                         AS [Total MB],
        sum((TotalExtents - UsedExtents) * 64.0 / 1024.0)   AS [Free MB]

    from #filestats     AS f
        join #filegroup AS g
            on f.filegroup = g.groupid

group by g.groupname


-- Clean up after self
drop table #filestats
drop table #filegroup

set nocount off

Thursday, June 14, 2012

EDMX ProviderManifestToken issue between 2005 & 2008

We are in the process of upgrading our SQL Servers to 2008.  While this is going on we still need to maintain and release code to the 2005 boxes.

Our team doesn't do much C# or Entity Framework work, but instead we work mostly in SQL and the BI environment.  We were assigned to update one of the C# projects with a few new fields since we were the recipients of the data.

We spent the morning trying to figure out why code that was working just fine in our new development, qa, and production (2008) environments suddenly quit when promoted back to the older 2005 production system.

All we did was to update the Entity Model to add a couple more integer columns. 

Really, nothing major.  Honest.

Upon doing a stack trace we discovered that it was failing on a datetime2 conversion.  Now, we are not even using a DateTime2 in the sql2008 let alone could we find any reference to it in our project and we know we didn't add that as a data type.

After a bit of investigation (source control to our rescue) we discovered that the before and after versions of one line was causing our problem.

In our original EDMX XML we saw:

ProviderManifestToken=”2005”

 but the new code had:

ProviderManifestToken=”2008”

We manually edited the EDMX XML and the program was back up and running again.

 So I guess the act of editing the Entity Model while working in SQL 2008 was our downfall.  At least we'll be live at the end of the month and good bye to 2005.



Thursday, May 10, 2012

SSIS msdb.dbo.sysSSISPackages

My coworker and I are in the process of converting a bunch of 2005 SSIS Packages to 2008 SSIS.

These packages are stored in the msdb database in the dbo.sysSSISpackages table.

The following msdn link shows all the columns and their descriptions:
                                 http://msdn.microsoft.com/en-us/library/ms181582.aspx

We needed to get to the raw XML for one of the packages as it didn't appear to be in our source control.  The above link told us that the code is stored in the column PackageData.  Unfortunately, it appeared to be encoded.


After a bit of searching I found the following code to convert the code back to the XML.



--------------------------------------------------------
-- Get XML from encoded column
--------------------------------------------------------


SELECT 

  Name
, CONVERT( xml, CONVERT(varbinary(max),PackageData) ) 
   AS XMLSource

FROM msdb.dbo.sysssispackages 


Tuesday, April 10, 2012

Synonyms causing editor slowness

So, today we've been tasked with a problem with slowness in the Query editor for one of our users.

She uses the "Design Query in Editor" option of SSMS.  The editor was taking twenty seconds to appear every time she does this on a certain database.  On some of the other databases the Designer was extremely fast.

In researching this we discovered a couple of known bugs.  The first was due to the number of synonyms used in the database.  In this particular case, we have over 2200 synonyms.  It appears, according to Microsoft that this is a known issue with a large number of synonyms.  After some testing, the number of synonyms in a database dramatically affected the speed of the designer.  As the number of synonyms increased the time increased dramatically.


The second issue was that none of the synonyms actually showed up in the Designer.  The designer has four tabs, tables and synonyms being two of them.  The table tabs works just fine, but the synonym tab is empty.  Again, after consulting the Microsoft site; we have found another known issue.

Unfortunately, for us these don't appear to be issues they are planning on resolving anytime soon.


The following items were found on the Microsoft site.

Slowness in Query editor due to synonyms  (known bug.  Won’t be fixed):

Synonyms not showing in Query editor… Synonyms tab  (Known Bug.  Won’t be fixed)





Tuesday, February 7, 2012

Displaying Size of Sql Server database tables


We are in the process of refining some of our data types.  As part of this, we needed to know the space usage and row count of each table in the database before we begin.


We were able to use the system stored procedure: sp_spaceUsed.

The sp_spaceUsed stored procedure can be passed an argument:

        sp_spaceUsed 'myTable'

Which will then return the stats for that table: including the number of rows, table size and index size.

Using this stored procedure, along with the undocumented sp_MSforEachTable we can loop through all the tables in the database and dump the results into a temp table.


See the code below:



-- Create Table to hold all tables and their values
CREATE TABLE #TableUsage
(
      Name      nvarchar(128)
    , rows      int
    , reserved  varchar(50)
    , data      varchar(50)
    ,index_size varchar(50)
    ,unused     varchar(50)
)   

-- Use the undocumented stored procedure
    -- sp_MSforEachTable  with the sp_spaceUsed procedure
    INSERT INTO #TableUsage
    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"



-- Now view the result set
Select * FROM #tableUsage






















Wednesday, January 18, 2012

SQL Server - Create Alter Table Statements quickly


This is a follow-up to my post for listing all columns with a specific data type.

The goal of the query below is the quickly create all the needed Alter Table statements for the new user defined data type that I'm going to be replacing the old one with.

As stated previously, we are planning on upgrading an older SQL Server 2005 server to SQL Server 2008.  In the process we determined that a couple of the columns would need to have their User Defined Data Types updated.

Instead of manually coding the Alter statements, I spent a few minutes creating a select statement that would allow me to copy the results to another SQL window with all the code needed to make the change.

In the process; I like my code to look pretty, oh so pretty.  So I'm using a couple of replicate statements to pad out spaces for the code to be created.



-- Get the Maximum TableName Length for the specificed type I'm going to update
DECLARE @MaxTableName int
    SET @MaxTableName = (
                   
                            SELECT MAX(LEN(o.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )


-- Get the Maximum Column Name length for the type I'm going to update
DECLARE @maxColumnName int
    SET @MaxColumnName =
                        (
                            SELECT MAX(LEN(c.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )   


-- Now create the pretty Alter statements

-- Will just need to copy the results to a new sql window and
-- you will have all the code needed to fix your problem
            SELECT ( 'ALTER TABLE dbo.['
                            + RTRIM(LTRIM(o.Name))
                            + ']'
                            + REPLICATE (' ',@MaxTableName-LEN(o.Name))
                            + ' ALTER COLUMN ['
                            + RTRIM(LTRIM(c.Name) )
                            + ']'
                            + REPLICATE (' ',@MaxColumnName-LEN(c.Name))
                            + ' DateTime NULL'
                )

            FROM sys.columns AS c
            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
            Where   t.name = 'Date'
                and t.is_user_defined = 1
                and o.Type = 'U'
               
            ORDER BY c.OBJECT_ID




So using the above code I got the resulting output:


ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date1]   DateTime NULL
ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date20]  DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date300] DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date4]   DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date50]  DateTime NULL





SQL Server - List all columns with a specific data type

We are in the process of upgrading an old server from SQL Server 2005 to SQL Server 2008.  In the preliminary investigation stage we discovered that there  are a couple User Defined Data Types that are named "Date" and "Time".

As these are now system data types we needed to determine which tables and columns would be affected by the upgrade.

Come to find out, this was a pretty simple task.  Using the system tables; Objects, Columns and Types we were able to pull a list together very quickly of the tables and columns that needed to be updated:


-- Select all Columns which have the user defined type name of 'Date'
SELECT
         SCHEMA_NAME(t.schema_id) AS SchemaName
        ,o.Name                   AS TableName
        ,o.Type_Desc              AS TableDescription
        ,o.Type                   AS TableType
        ,c.Object_id              AS ColumnObjectID 
        ,c.name                   AS ColumnName

        ,c.User_Type_ID
        ,t.name                   AS TypeName
        ,t.is_user_defined
    FROM     sys.columns AS c
        JOIN sys.types   AS t ON c.user_type_id = t.user_type_id
        JOIN sys.objects AS o ON c.Object_id    = o.Object_ID
    Where   t.name            = 'Date'  -- Type Name here
        and t.is_user_defined = 1       -- Yes, we want User Data Types only
        and o.Type            = 'U'     -- User Table Types only
   
ORDER BY o.name,c.name;

   

Well, we had a gotcha here...  The system not only saves which tables and columns that link to the UDT but all Indexes, Statistics, Stored Procedures, functions and views point to the UDT.  To see all affected types just comment out the last AND statement in the above script.

What we ended up having to do was

  • Create new UDT
    • You can not just change a UDT, it has to be created and old one dropped
    • Also means new UDT can not have same name, of course you could get creative here and add a couple of steps to the process so as to retain the same name.
  • Dropping the Statistics
  • Disabling and in some cases having to drop the individual indexes involved
  • Then Altering the table
  • Rebuilding or recreating the indexes
  • Recreate the statistics
  • Recompile the Stored Procedures and UDF's
  • Recreate any affected Views
  • Then you can drop the old UDT

It was strange, even though they still point to the 'date' UDT the functions still worked.


Seems to me, that the the UDT's aren't accomplishing their main objective and that is making it easier to maintain consistency across tables.


For example:

You have a Social Security Number of 11 digits (9 digits plus the two dashes).  You created a UDT for SSN of char(11).  This would allow you to have any tables with a SSN in it to be marked with this UDT and make sure that all are a consistent 11 digits.

Now, the government decides that they need to add a couple more digits to the mess.  So we are tasked with finding and updating all SSN to this new length format.  The UDT makes this very simple to locate all affected columns.

But, updating this column isn't as simple as changing the UDT.  Instead, you have to some major changes through out the database(s).















Monday, January 16, 2012

SQL Server - Check to see if a table is locked

How to check to table locking in SQL Server 2008 R2.

We used to use SP_LOCK and SP_LOCK2, but have since learned that the recommend method to determine locks is the code below as the two procs are to be deprecated in future releases.

Microsoft says the better way is to use the sys.dm_tran_locks dynamic management views.



The following code will select from the sys.dm_tran_locks and sys.partitions system tables.

More information on Locks can be found at the following Microsoft library pages:

Lock Modes

Locking Granularity

Key Range Locking 




 
---------------------------------------------------------------
--  Code to find out what table is locked and the lock reason
---------------------------------------------------------------
select 
    object_name(P.object_id) as TableName
    , resource_type
    , resource_description
    , request_mode
    , CASE REQUEST_MODE
                    WHEN 'S'        THEN 'Shared'
                    WHEN 'U'        THEN 'Update'
                    WHEN 'X'        THEN 'Exclusive'
                    WHEN 'IS'       THEN 'Intent Shared'
                    WHEN 'IU'       THEN 'Intent Update'
                    WHEN 'IX'       THEN 'Intent Exclusive'
                    WHEN 'SIU'      THEN 'Shared Intent Update'
                    WHEN 'SIX'      THEN 'Shared Intent Exclusive'
                    WHEN 'UIX'      THEN 'Update Intent Exclusive'
                    WHEN 'BU'       THEN 'Bulk Update'
                    WHEN 'RangeS_S' THEN 'Shared Range S'
                    WHEN 'RangeS_U' THEN 'Shared Range U'
                    WHEN 'RangeI_N' THEN 'Insert Range'
                    WHEN 'RangeI_S' THEN 'Insert Range S'
                    WHEN 'RangeI_U' THEN 'Insert Range U'
                    WHEN 'RangeI_X' THEN 'Insert Range X'
                    WHEN 'RangeX_S' THEN 'Exclusive range S'
                    WHEN 'RangeX_U' THEN 'Exclusive range U'
                    WHEN 'RangeX_X' THEN 'Exclusive range X'
                    WHEN 'SCH-M'    THEN 'Schema-Modification'
                    WHEN 'SCH-S'    THEN 'Schema-Stability'

        ELSE NULL
        END AS REQUEST_LOCK_MODE

FROM   sys.dm_tran_locks   AS L
       join sys.partitions AS P
        on L.resource_associated_entity_id = p.hobt_id








Thursday, January 12, 2012

Write Function in SQL Server 2005

Something cool I just ran across today.  It appears that in SQL 2005 they added the 'Write' function.

This allows you to update character column, including that with the size of MAX.  This is useful as STUFF didn't work with varchar(max).


-- Create a Temp table to hold our text
CREATE TABLE #tmpTable
    (
        myText NVARCHAR(MAX)
    )


-- Insert sample text we want to mess with
INSERT INTO #tmpTable
Select 'This a very cool Test!'


-- Use the Write function to put the text 'Hello Test'
-- at the beginning of the field
UPDATE #tmpTable
    SET myText.write(
                        'Hello Test'
                        ,0              -- Start index
                        ,0              -- Number of characters to replace
                    )


-- Show that the text has indeed been pushed to the front of the field
select * FROM #tmpTable


-- Now lets use the Write function to delete the first ten characters
UPDATE #tmpTable
    SET myText.Write(
                        ''              -- An Empty String
                       , 0              -- Start at 0
                       , 10             -- The number of characters to replace
                    )

















                   

Tuesday, January 10, 2012

SQL Server Agent System Stored Procedures

A followup post to Sql Server Agent Tables.

In addition to the tables, the are already system stored procedures that will get the information for you.  I've included some of the man arguments.  More information on the stored procedures can be seen at the Microsoft msdn link.

  • sp_help_job
    • if you run this by itself it will show all your jobs
    • Arguments:Or you can Pass in the @job_id or @job_name 
  • sp_help_jobActivity
    • shows the status of the job run.  
    • Arguments:  @job_ID or @job_Name
  •  sp_help_jobHistory
    • shows all the history information for all of the job runs
    • Arguments:  @job_ID or @job_Name
  • sp_help_jobCount
    • Arguments: @schedule_id or @Schedule_name 
    • it will return a count o how many jobs a schedule is tied to.
  • sp_help_jobs_in_schedule
    • Arguments: @schedule_id or @Schedule_name 
    • it will return a a list of all jobs tied to that schedule.sp_help_job_schedule
  • sp_help_jobSchedule
    • Shows jobs that are linked to a schedule
    • Arguments: @job_id or @job_name
  •  sp_help_jobServer
    • Shows information about server tied to a job
    • Arguments: @job_id or @job_name
  • sp_help_jobStep
    • Shows information about steps tied to a job
    • Arguments: @job_id or @job_name
  • sp_help_jobStepLog
    • Shows information about a specific job steplog
    • Arguments: @job_id or @job_name 
    •                  @step_id or @step_name
  • sp_help_schedule
    •  Shows information for schedule
    • Pass in the @schedule_id or @Schedule_name or no parameters for all







Saturday, January 7, 2012

Sql Server Agent Tables

SQL Server stores the SQL Server Agent Jobs in various tables in the msdb.  This provides you an easy way to view jobs, their steps as well as their run history.


  • sysJobs
    • Stores the name, job_ID and related information
  • sysJobSchedules
    • Shows the schedules for the jobs
    • Arguments: Uses Job_ID to link to sysJobs
  • sysJobSteps
    • Shows each step in a job.  Includes the command (actual code used), database and related information
    • Arguments: Uses job_id to link to sysJobs
  •  sysJobHistory
    • Shows the  run history for that job, status, date, run time and duration to complete
    • Arguments: Uses Job_ID and step_ID
  • sysJobServers
    • Stores server related information for jobs
  • sysJobActivity
    • Stores data about the job activity
  • sysJobStepsLogs
    • If it is enabled, it will show specific job step information


Code used to get various information:
use msdb

DECLARE @jobID varchar(50)

-- Get the jobID for a specific job
SET @jobID = ( select job_ID FROM sysJobs where name = 'ETL_Test' )

-- Get the job Information
SELECT * FROM sysJobs     where job_id = @jobID

-- Get the steps for that job
Select * from sysJobSteps where job_id = @jobID order by step_id


-- Show all jobs that use the storedProcedure xs_myTest
SELECT * FROM sysJobSteps where command like '%xs_myTest%'





In addition to the tables there are also System Stored Procedures.  

SQL Server: how to delete a table in chunks

 
I was asked the other day how someone should delete all of rows in a very large table when they couldn't use the TRUNCATE function because there were foreign keys tied to that table.

Deleting all the tables in a single step could cause a large transaction and cause lots of locks.

I found the code below a while back on a better way to minimize the maximum locks and resources on the server.  Deleting in smaller chunks creates a bunch of small transactions that is more manageable for the server to handle.

In addition, if you happen to have an error on one of the chunks, all the deletes up to then are retained and not rolled back, only that one chunk is rolled back.   Then again, if you have an error, all prior deletes are not recoverable.


For this example I'll create a table, populate it with a small set of tables, then the delete command is what we actually use to delete it in chunks.

--- Create table to hold data
CREATE TABLE #Cars (id int)

--- Add a number of Rows to table
DECLARE @x INT
SET @x = 1
WHILE(@x < 10000)
BEGIN
    INSERT INTO #Cars Select round((rand()*100),0)
    SET @x = @x + 1
END

   
--- Delete all the rows in chunks of 5000
--- If the number of ROWS processed in the DELETE statement are not Zero 
--the repeat the delete statement

DeleteThem:
    DELETE TOP(5000) FROM #Cars
if @@RowCount != 0
goto DeleteThem   










Friday, January 6, 2012

SQL Server - Altering tables and renaming columns

 A quick post on how to alter the data types and nullable attributes for a table.

In addition, since you would think that the same command statement would also take care of renaming a column I've added that as well.

First we create a test table to work with, as usual I'll stick with cars.

 After the table has been created, we realized that the Make is only five characters.  That will need to be fixed.

For some reason, it was decided that the Year can not be nullable.  They don't care about the Make or Model, go figure.

Then finally, we are told that Year really shouldn't be used.  They want us to use ModelYear instead.  But wait, ALTER TABLE just doesn't allow column name changes.  So we must use the system stored procedure SP_RENAME.

Below we'll be using SP_RENAME to rename the column.  But it can also be used to rename databases, indexes, objects and user data types.  See: msdn:SP_RENAME for more information.

 
-- Below creates a sample table called cars:

--- Create a Table ---
CREATE TABLE dbo.Cars
(
    ID     INT          Identity(1,1) NOT NULL
  , Year   INT         NULL
  , Make   VarChar(05) NULL
  , Model  VarChar(50) NULL
)

GO

-- the Alter TABLE statement allows you to modify the
-- data type and the nullable attribute of a column.


-- Here we are increasing the size of the Make
-- column to 50 characters from the initially created 05
ALTER TABLE dbo.Cars
    ALTER COLUMN Make Varchar(50) NULL
   
 
GO    

-- Let's prevent null values from being entered
-- into the Year column. 
--Note: If there are already null values in the
--Year column, this command would fail.
ALTER TABLE dbo.Cars
    ALTER COLUMN Year INT NOT NULL

GO

-- There is no ALTER command to rename a column
-- for this you will need to use 'sp_rename'
EXEC sp_rename 'dbo.Cars.Year', 'ModelYear', 'column'


--Note: You will receive a caution message indicating that
--      your change could break any existing scripts or
--      stored procedures.  So make sure you update any
--      affected stored procedures or scripts.