Latest Event Updates
There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.
Here are 3 methods but not limited.
1. Get a specific table size
EXEC sp_spaceused N’dbo.MyTable’
2. Get all table size
CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’
SELECT * FROM #TableSize ORDER BY Name ASC
3. Custom Code
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.OBJECT_ID > 255
GROUP BY t.Name
ORDER BY t.Name
Image Posted on Updated on
When you’re displaying data in a Table or Tabular, sometimes the report may look very busy to the eyes. By alternating the row colors, the results will look more easy on the eyes. Here’s how you do it.
Step 1: Click on the Design tab
Step 2: Click on the detail row. In the BackgroundColor drop-down, select “Expression”
Step 3: Type this =IIF(RowNumber(Nothing) MOD 2,”White“, “Silver“)
Step 4: Preview the report
Image Posted on Updated on
Use Conditional Split if you need to separate the data from a source (Flat File, Excel, Table, etc.) into multiple destinations (Flat File, Excel, Table, etc.)
Step 1: Drop a Data Flow Task to the Control Flow
Step 2: Create 4 Flat File Connections. The first flat file is your source. You can use OLE, ODBC, etc.
This second flat file is for my all .txt data
This third flat file is for my all .cs data
This fourth flat file is for my all .aspx data
Step 3: Drop a Flat File Source into the Data Flow
Step 4: Double click on the Flat File Source and establish the connection by selecting your source file.
Step 5: Drop a Conditional Split into the Data Flow. Drag the arrow from the Flat File Source to the Conditional Split.
Step 6: Double click on the Conditional Split and create three conditions.
Step 7: Drop three Flat File Destination onto the Data Flow
Step 8: Drag the arrow from the Conditional Split onto one of the Flat File Destination. An Input Output Selection dialog will popup. Select one of the output.
Repeat this step for all Flat File Destination.
Step 9: Double click on the Flat File Destination and select the connection. Repeat this process to the other two Flat File Destination
Step 10: Run the SSIS package and now you’ll have all three files with specific file types in them.
In this example, I’m using Flat Files but you can use Excel, ODBC, CSV, etc. The process is the same– just the connection setup is different.
Image Posted on Updated on
When you need to read through a file directory and save all of the file names to a text file, follow these steps.
Step 1: Create a variable to hold the file name that the ForEach Loop reads
Step 2: Create a Flat File Connection
Step 3: Drop a “Foreach Loop Container” to the Control Flow
Step 4: Rename the “Name” text to something more meaningful
Step 5: In the Enumerator drop-down, select “Foreach File Enumerator” and pick the folder path. Note: If you check “Traverse subfolders”, the Foreach loop will go through all subfolders.
Step 6: We need to save our results to a variable if we want to know what files were scanned. Under the “Variable” drop-down, select “User::MyFileName”
Step 7: Drop a “Script Task” into the Foreach Loop Container.
Step 8: Double click on the “Script Task”. In the “ReadOnlyVariables”, select “User::MyFileName”. Click on “Edit Script”
Step 9: Add the following C# code
public void Main()
ConnectionManager cm = Dts.Connections[“Output”];
string content = Dts.Variables[“User::MyFileName”].Value.ToString();
string fileDirectory = cm.ConnectionString;
System.IO.File.AppendAllText(fileDirectory, content + “\r\n”);
Dts.TaskResult = (int)ScriptResults.Success;
Now run your SSIS and once completed, open up the Output.txt file to see the results.
- SET TRANSACTION ISOLATION LEVEL XXXXX is for the entire script
- WITH (NOLOCK) is for that specific table
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM MyTable WITH (NOLOCK)
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting innonrepeatable reads or phantom data. This option is the SQL Server default.
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
If you need to get all SSRS that has a Subscription.
SELECT SubscriptionOwner = u.UserName
,ScheduleName = sch.Name
,ReportName = c.Name
,ReportPath = c.[Path]
,ReportDescription = c.[Description]
FROM ReportServer.dbo.Subscriptions AS s
INNER JOIN ReportServer.dbo.Users AS u ON s.OwnerID = u.UserID
INNER JOIN ReportServer.dbo.[Catalog] AS c ON s.Report_OID = c.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule AS rs ON s.Report_OID = rs.ReportID
AND s.SubscriptionID = rs.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule AS sch ON rs.ScheduleID = sch.ScheduleID
ORDER BY u.UserName
I always find myself recreating a States table for web applications. Here it is.
CREATE TABLE [States](
[StatesID] [int] IDENTITY(1,1) NOT NULL,
[Abbreviation] [nchar](2) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
( [StatesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
INSERT INTO [States] VALUES
(‘DC’, ‘District of Columbia’),
(‘NH’, ‘New Hampshire’),
(‘NJ’, ‘New Jersey’),
(‘NM’, ‘New Mexico’),
(‘NY’, ‘New York’),
(‘NC’, ‘North Carolina’),
(‘ND’, ‘North Dakota’),
(‘PR’, ‘Puerto Rico’),
(‘RI’, ‘Rhode Island’),
(‘SC’, ‘South Carolina’),
(‘SD’, ‘South Dakota’),
(‘WV’, ‘West Virginia’),