Blog

All posts tagged 'SQL'

Check out all of the posts tagged with 'SQL' below. If you still can't find what you are looking for, try using the search box.

SQL Azure Table Size

I've been fighting with a DotNetNuke install hosted on Azure for a while.  We've been testing as there's a lot we like about Azure but the performance when editing DotNetNuke has caused us to go a different route...but that's another story.  In our test site, the SQL database size just kept growing.  To find out the culprit, I wanted to know the size of each table.  This post from Alexandre Brisebois did just what I needed.  My only tweak was to Order By the size.

SELECT    
      o.name AS [table_name], 
      sum(p.reserved_page_count) * 8.0 / 1024 / 1024 AS [size_in_gb],
      p.row_count AS [records]
FROM   
      sys.dm_db_partition_stats AS p,
      sys.objects AS o
WHERE    
      p.object_id = o.object_id
      AND o.is_ms_shipped = 0
      
GROUP BY o.name , p.row_count
ORDER BY size_in_gb DESC

For me, it was pretty clear that I needed to truncate the DotNetNuke EventLog and ScheduleHistory.  In production, you would want to schedule this as it's amazing how quickly these can grow.  We hadn't put this site into production yet but the EventLog was 20 GB after just a few months.

truncate table EventLog
truncate table ScheduleHistory

This may or may not be related to anything you need, but for me in this SQL Azure situation, I noticed that even though I had truncated about 20 GB's of data the Azure Dashboard didn't reflect that.  Digging around it seems to be related to my indexes being fragmented.  So, following Dilkush Patel's post I ran this query to see my fragmentation. (Note: I did make a minor change to Order By the % of fragmentation.

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ips.avg_fragmentation_in_percent desc, ps.object_id, ps.index_id

For me, I had several tables over 60% fragmented.  So I ran Dilkush's script:

DECLARE @TableName varchar(255)
 
 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )
 
 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0
 
 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down to rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END
 
 CLOSE TableCursor
 DEALLOCATE TableCursor

However, after running this (several times in fact) the usage in my Azure Dashboard has not changed.  I'll wait and see if it changes and update this post if it does.

Popular tags: , ,

data loss might occur - SQL single user mode

I've been having challenges Publishing Lightswitch apps when selecting the option to "update an existing database".  For some reason, the Publish will at times run and run and never finish forcing me to kill Visual Studio.  I'm still working on the cause of that issue but when it does work, if you've deleted table rows in your LS app, you'll get an error when you try to Deploy on IIS that says:

Rows were detected. The schema update is terminating because data loss might occur.

Once you get this your database is now stuck in Single User Mode.  Here's the quick fix:

Open a query window in SQL Management Studio.

USE [master]

ALTER DATABASE [your locked DB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

 

 

Popular tags: ,

Windows 2008 R2 Firewall with SQL Server

By default, installing SQL Server 2008 R2 on a brand new Windows Server 2008 R2 server does not open the required Windows Firewall port.  I always wonder why they don't give you the option and install to have MS make the changes for you.  At any rate, MS has a tool to "Fix It" but on my Windows Server 2008 R2 it ran but said it didn't apply to my setup. ???? 

You can of course follow Microsoft's KB articles and manually add the Windows Advanced Firewall rules.  For me, a script to do this was the way to go.  Rolly Perreaux had a great post on setting up SQL and he had the script I've been using to open all SQL ports for my Domain profile on SQL servers.  Here's that script.   

SQL Firewall Ports Script

@echo *** OPENING SQL SERVER PORTS ***
netsh advfirewall firewall add rule name="SQL Server (TCP 1433)" dir=in action=allow protocol=TCP localport=1433 profile=domain
netsh advfirewall firewall add rule name="SQL Admin Connection (TCP 1434)" dir=in action=allow protocol=TCP localport=1434 profile=domain
netsh advfirewall firewall add rule name="SQL Service Broker (TCP 4022)" dir=in action=allow protocol=TCP localport=4022 profile=domain
netsh advfirewall firewall add rule name="SQL Debugger/RPC (TCP 135)" dir=in action=allow protocol=TCP localport=135 profile=domain
netsh advfirewall firewall add rule name="SQL Browser (UDP 1434)" dir=in action=allow protocol=UDP localport=1434 profile=domain

@echo *** OPENING ANALYSIS SERVICES PORTS ***
netsh advfirewall firewall add rule name="Analysis Services (TCP 2383)" dir=in action=allow protocol=TCP localport=2383 profile=domain
netsh advfirewall firewall add rule name="SQL Browser (TCP 2382)" dir=in action=allow protocol=TCP localport=2382 profile=domain

@echo *** OPENING WEB SERVER PORTS ***
netsh advfirewall firewall add rule name="Web Server HTTP (TCP 80)" dir=in action=allow protocol=TCP localport=80 profile=domain
netsh advfirewall firewall add rule name="Web Server SSL (TCP 443)" dir=in action=allow protocol=TCP localport=443 profile=domain

Pause

Enjoy!

Popular tags: , ,