A response from Lightspeed

Remember this post? Well, I emailed Lightspeed. Their response?

"By default, the database is set to autogrow and autoshrink to save space."

In the last week, I have had the following.

Initial size 5430MB
Manually grown database to 8000MB
Autogrow to 8800MB
Autoshrink to 5430MB
Autogrow to 5900MB

There is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend that SQL be installed on the C:\drive running under the local system account).

Moreover, autoshrink is set to false for every single database on the server, so I don't know what exactly is causing this shrinkage, but it ain't SQL. In fact, I would posit that it is completely inaccurate to say that the databases are autoshrinking, as this shrinkage does not show up in the 'Data/Log files Autogrow/Autoshrink' events part of the Disk Usage reports for the databases. It just happens, it doesn't show up in the logs, and it is really rather annoying me.

Just because

Just because you can use spaces, and inverted commas, and all sorts of other rubbish in your tablenames, doesn't mean that you should.

Credentials, Proxies, and SSIS SQL Agent Jobs

What I want to do:
Give one user access to see and execute one SSIS package as a SQL Agent Job, but none of the rest. Although the package exports data from one database on Server A to a new database on Server B, this does not count as a multi-server job in SQL terms. I've assumed that the package itself has been created and works as a SQL Agent Job under a sysadmin login, but I don't want to give this guy sysadmin access. He's a developer.

1. Create the credential, based on an existing login, on Server B. If you use a Windows login, it will want the correct password for that login, so don't get clever and make one up (can you spot the mistake I made?).





















2. Make sure that the credential's login has the correct access on Server B
3. Create the proxy, point it at the credential.
4. Add principals to the Proxy i.e. the accounts that you want to have the rights of the proxy when executing the SQL Agent Job.



















5. Create the job, and make the user's Login the owner of the job.
6. Give the user's Login the following rights in MSDB: db_ssisoperator and SQLAgentUserRole
7. Test.

I found it very helpful to test this process out using my non-sysadmin Windows account: it meant I could test out the process while being able to see all error messages on my own machine.

Doubtless, there are better explanations of how to do this out there: and I am not sure what I would do if I needed to allow multiple users to execute the same package under such constraints. I am sure someone will let me know...

But what if the FTS won't?

When building a Full Text Search Catalog and Index, it is a Good Idea to check that the search works properly at the end of it, by using something along the following lines:

SELECT *

FROM tablename

WHERE Freetext (COLUMN, 'some text')

Now, I knew the create script worked, as I'd used it to create my Very First Full Text Catalog and Index only a week ago. I knew the select would work, because, again, I'd tested it. SQL 2008 SP1 was about to have a bit of a disagreement with me about what was, and what wasn't, going to work.

Not so.

SQL Server encountered error 0x80070005 while communicating with full-textfilter daemon host (FDHost) process. Make sure that the FDHost process isrunning. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.


and in the Logs:


SQL Server failed to communicate with filter daemon launch service (Windows error: Windows Error: hr = 0x80070005(failed to retrieve text for this error)). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Running sp_fulltext_service 'restart_all_fdhosts' did not improve matters: the command ran happily, but the error persisted. Restarting SQL didn't help either (heck, it's a DEV box, and not yet ready for the Developers).

Searching for 0x80070005 told me that the problem was to do with permissions: and the solution turned out to be setting the SQL Full Text Filter Daemon Launcher Service to run under the same account as the SQL Server Service.

The SELECT statement then deigned to execute: but did not return any results. Rebuilding the index did not help matters: however, dropping the FULLTEXT index, the index, and the Full Text catalog did work. T-SQL here for posterity for next time I have this problem.



DROP FULLTEXT CATALOG CatalogName

DROP FULLTEXT INDEX ON dbo.TableName

DROP INDEX IndexName ON dbo.TableName

When the autoshrink/autogrow won't show in SQL Reports

...Try the script below, from this discussion on MSDN. It worked beautifully for me when it turned out that there were well over a million rows of autogrow/autoshrink data, and thus the autogrow/autoshrink data from SQL 2008's built in reports wasn't going to show correctly.

I then changed the data file size, so that the thing grew in one BIG chunk, gave it lots of elbow room for more data to fill up the file, and left it well alone.

DECLARE @filename VARCHAR(100), @filenum int

SELECT @filename = CAST(value AS VARCHAR(100))

FROM fn_trace_getinfo(DEFAULT)

WHERE property = 2

  AND traceid = 1

  AND value IS NOT NULL



-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'



--Check if you have any events

SELECT gt.HostName,

       gt.ApplicationName,

       gt.NTUserName,

       gt.NTDomainName,

       gt.LoginName,

       gt.SPID,

       gt.EventClass,

       te.Name AS EventName,

       gt.EventSubClass,

       gt.TEXTData,

       gt.StartTime,

       gt.EndTime,

       gt.ObjectName,

       gt.DatabaseName,

       gt.FileName

FROM [fn_trace_gettable](@filename, DEFAULT) gt

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

WHERE EventClass in (92, 93, 94, 95)

and gt.DatabaseName = 'tempdb'  --Change the DB Name here

ORDER BY StartTime;

Taking it futher

The awesome SQLRockStar came out with a nifty tip about fragmented Virtual Log Files. SQL has this tendency to split the transaction log file out into different chunks. It might be physically contiguous, but, logically, it is fragmented, and this affects performance.



The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.


Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.

Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.

USE [databasename]
-- Backup the Log File

BACKUP LOG [databasename] TO devicename
GO

-- Set recovery to simple mode, otherwise the log file refuses to shrink

ALTER DATABASE [databasename]
SET recovery simple
GO

-- Shrink the log file

DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO

-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]

MODIFY FILE

(

NAME = transactionloglogicalfilename

, SIZE = newtotalsize --MB

)
GO

-- Set recovery back to full mode

ALTER DATABASE databasename
SET recovery FULL
GO

Finally, run a full backup, so that all future transaction log backups have something to work from.

(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)

Scrap of SQL

How to identify duplicates in a table....


SELECT email,

Count(email) AS numoccurrences

FROM users

GROUP BY email

HAVING (Count(email) > 1)

Of course, what to do with all those duplicates is up to you!