SELECT

o.name

, indexname=i.name

, i.index_id  

, reads=user_seeks + user_scans + user_lookups  

, writes =  user_updates  

, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

                  WHEN s.user_updates < 1 THEN 100

                  ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

  END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'

FROM sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  

INNER JOIN sys.objects o on s.object_id = o.object_id

INNER JOIN sys.schemas c on o.schema_id = c.schema_id

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

AND s.database_id = DB_ID()  

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000

ORDER BY reads

Transact SQL Code Library

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Why is my transaction log full?

By Gail Shaw, 2011/02/10

It’s an error that’s near-certain to ruin any DBA’s day:

Error: 9002, Severity: 17, State: 2.

The transaction log for database 'VeryImportant' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

“But, but, but...” mutters the DBA, “The log backups are running, so why is the log full?”

Well, failed or missing log backups are just one of the reasons for a transaction log to be full. There are several other possible causes.

Where to start?

Start by querying sys.databases and to see what the value of the column log_reuse_wait_desc is for the database mentioned in the error message.

 DECLARE @DatabaseName VARCHAR(50);
 SET @DatabaseName = 'VeryImportant'

 SELECT name, recovery_model_desc, log_reuse_wait_desc
   FROM sys.databases
   WHERE name = @DatabaseName

The value of the log_reuse_wait_desc column will show the current reason why log space cannot be reused. It is possible more than one thing is preventing log reuse. Sys.databases will only show one. Hence it is possible to resolve one problem, query sys.databases again and see a different log reuse wait reason.

The possible values for log_reuse_wait_desc are listed in Books Online. But first, before we get to an explanation of the various reasons, a short piece of theory.

Transaction log architecture

The transaction log is a circular file that's divided internally into a number of virtual log files (VLFs). SQL writes log entries sequentially into the VLFs. When one VLF fills up, SQL moves to the next one. When it reaches the end of the file, it will (ideally) go back to the beginning and start using the first VLF again (hence circular).

A VLF can be in one of two states:

1. Active
2. Inactive

Active

An active VLF is one that contains one or more log records that are needed by the database engine. The log records may be part of active transactions, they may be log records that are needed for replication or mirroring, they may be needed for a backup, they may be associated with changes that haven’t yet been written to the data file, etc.

Inactive

An inactive VLF is one that does not contain any log records needed by the database engine for any reason. There are no active transactions, the log records are not needed for replication or mirroring and all the changes associated with these transaction log records have been written to the data file. If the database is in full or bulk-logged recovery the log records in the VLF has been backed up.

Log reuse

For a VLF to be reused by SQL, it needs to be inactive. If all the VLFs in the log are active and SQL has filled the one it is writing to, the transaction log will grow or, if it cannot grow, data modifications will fail with error 9002. (The transaction log for database '<Database name>' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases)

The act of marking one or more VLFs as inactive is generally called log truncation. While this is a commonly used term, it’s a not a very accurate term. Truncate, according to the Oxford English dictionary means “shorten (something) by cutting off the top or the end”, however log truncation doesn’t shorten anything; it just makes the space available for reuse.

In addition, people often seem to think that truncate means to discard log records and break the log backup chain (probably from BACKUP LOG ... WITH TRUNCATE ONLY). It does not mean that.

In the above diagram, the transaction log has 4 VLFs (labelled A, B, C and D). This is for simplicity; most log files will have more. The first (A) and second (B) VLFs are inactive, and the third (C) and fourth (D) are active with SQL currently writing log entries into the "D" VLF. In that diagram, the (1) marks the log record of the last log backup, (2) marks the log record associated with the last checkpoint and (3) is the last log record written into the database.

After a number of data modifications, the log has wrapped around and SQL has now reused the first VLF and is about to start reusing the second. The third and fourth VLFs are still active. In this situation, unless something happens to mark the third and fourth VLFs as inactive the log will grow as soon as SQL has mostly used the second VLF. In this case, what is preventing the reuse is that the log backup has not run again.

If a log backup now runs, the third and fourth VLFs (C and D) would be marked inactive and would be available for SQL to reuse them.

Causes of delayed log truncation

These are the log reuse wait reasons from sys.databases

Active transactions

The active transaction log reuse wait reason means that there is an open transaction that is keeping the VLFs active.

When encountering this log reuse wait, two commands will help in identifying the source of the open transaction, DBCC OPENTRAN and sys.dm_exec_sessions

DBCC OPENTRAN lists information about the oldest open transaction in the database. The main piece of information needed from this, when investigating log growth caused by active transactions, is the SPID (Server Process ID) that is running the transaction.

Transaction information for database 'WebForums'.

Oldest active transaction:

SPID (server process ID): 51

UID (user ID) : -1

Name : user_transaction

LSN : (10861:3200:1)

Start time : Jan 14 2011 1:04:26:017AM

SID : 0x0105000000000005150000002e86f8cbc457a001b905c7e95e040000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In this example, the connection with SPID 51 has had a transaction open since 1AM on the 14^th Jan (a late worker, clearly). While there’s nothing in the output of DBCC OPENTRAN that can identify who is running that transaction, the SPID can be used along with some of the DMVs to get that information.

SELECT host_name,program_name, original_login_name, st.text
   FROM sys.dm_exec_sessions es
      INNERJOIN sys.dm_exec_connections ec
          ON es.session_id = ec.session_id
      CROSSAPPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
   WHERE ec.session_id = 51

I’m using original login name, rather than login name in case there’s any impersonation occurring.

That should be enough information to make an educated decision whether to kill the connection, or to speak to the person running the query and ask them to stop, or to wait for it to finish if it’s too important to be rolled back.

Checkpoint

The checkpoint log reuse wait should be a transient condition in most circumstances. It will be most commonly seen in simple recovery model.

SQL Server automatically runs checkpoints on a regular basis to keep the number of log records that it would need to process during a database recovery. It runs checkpoints for a number of other reasons as well. For a full list, see http://msdn.microsoft.com/en-us/library/ms188748.aspx

If the checkpoint log reuse wait appears frequently or for prolonged periods occur it may indicate that the IO subsystem performance is inadequate for the volume of data that needs to be written during the checkpoint or that there are very large numbers of changed pages that the checkpoint needs to process.

Database backup

The active portions of the log are necessary for database backups, as the backup has to include at least that much of the log to ensure a consistent restore. As such, long-running database backups may result in log growth as the portions of the log necessary for the backup must be retained for the duration of the backup.

There is little that can be done if this wait type is regularly encountered and is causing problems, other than optimising the backups themselves (optimising the IO subsystem or striping the backups over multiple devices), converting to file/filegroup backups (with appropriate consideration of backup/restore requirements) or using compressed backups either natively (SQL 2008 Enterprise, SQL 2008 R2 Enterprise and Standard editions) or via a 3^rd party tool, or other methods that reduce the time taken to back the database up.

Replication

Transactional replication uses the transaction log to identify changes that need to be replicated to subscribers.

Whenever a change is made to a replicated table, the log records associated with that change are marked as ‘pending replication’. The Log Reader Agent job, which is one of the components of transactional replication, reads along the log looking for such transactions and, when it finds one, it places the details of the change into the distribution database and marks the log entry as ‘replicated’.

If transactions are being marked ‘pending replication’ and the Log Reader is not running, then those log records will never be marked as ‘replicated’, and the VLFs that contain those log records will never become inactive, and the log will grow.

In addition to this, on SQL 2008 Enterprise edition, the Change Data Capture feature (CDC) uses the transaction log and the Log Reader Agent job in much the same way as transactional replication. Hence, if the CDC jobs are not running, the log space cannot be reused, much as if there was transactional replication with the Log Reader Agent job not running. NB, the log reuse wait description still shows Replication, not CDC.

The only kind of replication that uses the transaction log is transactional replication, and thus it is the only type of replication that can prevent log reuse. However, there was a bug in some versions of SQL 2005 that could result in a log reuse wait of Replication when the only replication present was snapshot replication. See http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx.

Database Mirroring

In database mirroring, the principal database sends log records to the mirror database. In synchronous mirroring, this occurs before the transaction is committed on the principal. In asynchronous mirroring, the log records are placed into a log send queue when the transaction is committed on the principal. The log records will then be sent to the mirror at some point afterwards.

If the mirroring is running asynchronous and the rate of transactions exceeds that which the link between the principal and mirror can handle, then the log on the principal will grow because the log records cannot be marked inactive until they have been sent to the mirror.

The same thing will happen in synchronous mirroring if the link between the principal and mirror drops and the mirroring becomes disconnected or if the mirroring session is suspended. The log records on the principal remain active until the mirror reconnects or the session is resumed and the log records are copied over to it, or until the mirroring is dropped.

Snapshot creation

This should also be a short-lived transient type. During the creation of a database snapshot, SQL has to read the source database’s log so that it can run crash-recovery on the snapshot in order to ensure that it is transactionally consistent. While it is doing this, the portions of the log necessary cannot be marked as inactive.

DBCC CHECKDB uses hidden database snapshots for its checks and so this wait may appear when running CheckDB even if there are no user-created snapshots.

Prolonged snapshot creation waits can be a result of very long-running transactions that need to all be rolled back for the snapshot creation, or an inefficient reading of the log due to a very large number of VLFs.

Log backup

Log backups are the one that most people know can prevent log reuse. In full and bulk-logged recovery models, VLFs cannot be marked as inactive until all the log records in that VLF have been backed up. The log backup wait reason will never appear in Simple Recovery.

If this log reuse wait reason appears, check that the log backups are correctly scheduled and are succeeding. Also check that the NO_TRUNCATE option has not been specified, as that option is only for backing up the log of a damaged database and will not ‘truncate’ any VLFs (as the option name implies).

Conclusion

This should cover the main reasons why a database’s transaction log may grow. Fixes for more complex problems are out of scope of this article, but this should at least help in knowing where to start looking.

Acknowledgements

Firstly a massive thank you to Paul Randal (blog|twitter) who kindly agreed to tech-edit this article and corrected a variety of errors small and large. Without his assistance this article would not have been worth reading.

Also thank you to Robert Davis (blog|twitter) who provided some comments and corrections around log use in database mirroring.

Finally thanks to Jason Brimhall (CirquedeSQLeil) (blog | twitter), Gus Gwynne (GSquared), Brandie Tarvin (blog|twitter), Wayne Sheffield and Stefan Krzywicki for correcting grammar errors, illogical sentences and an appalling dearth of punctuation.

As they say in published books, any errors that remain are solely mine.

Changing Server Collation on SQL Server 2008

By Blesson John, 2010/09/20

  Rate this |   Join the discussion |   Briefcase |   Print

Editor: Please review my edits.

This article is dedicated to the Senior DBA Nick Mcnair,who showed me the command to alter server level collation.nRecently I hit a problem were the user database had a different collation from that of the server level collation. In this article we wil look at how to change the server level collation for a clustered SQL Server. When one changes the server collation he/she is rebuilding the system databases. This means you would lose references to user databases, jobs, SSIS packages, etc. In order to avoid the loss of information and reduce downtime, it is mandatory that a step by step procedure is followed.

On a side note the database collation can be changed using the command given below

ALTER DATABASE <database_name> COLLATE <collation>;

ALTER DATABASE adventureworks COLLATE SQL_Latin1_General_Cp850_BIN;

It is worth remembering that character fields such as varchar,char,text etc will have the old collation even after the above command is executed.

I have listed below some steps that I had to follow in order to change the server level collation.The steps given below aren't an exhaustive list, but they worked for me.

1) Run sp_configure to find the current server settings,for this you have to set 'show advanced option' to 1.

exec sp_configure 'show advanced options',1

reconfigure with override

Now run the sp_configure command to extract the server settings.

exec sp_configure

Copy the output into an excel sheet and save it in a safe location.You would need this after the collation has been changed.

2) Go through each of the user databases and check whether any user defined assemblies exist. Make a note of all the databases that have user defined assemblies.

 

3) Script out all the jobs on the server.

4) Check the database maintenance plan and make sure that you have noted down the schedules for each step in the maintenance plan.

5) Export all the SSIS packages to the file system.

6) check whether any mail profiles are present and copy the details of each profile to a table on one of the user databases.

(use msdb's sysmail_account,sysmail_profile,sysmail_profileaccount and sysmail_server to retrieve profile related information).

In order to create profile and account you can manipulate the code excert given below

BEGIN TRAN

SET @DESCRIPTION = 'give description about the profile ';

SET @DESCRIPTION = @DESCRIPTION + @V_MAIL_PROFILE ;

-- Create a Database Mail profile 

 EXECUTE msdb.dbo.sysmail_add_profile_sp 

 @profile_name = @V_MAIL_PROFILE, 

 @description = @DESCRIPTION;

 SET @DESCRIPTION = 'give description about the account ';

 SET @DESCRIPTION = @DESCRIPTION + @V_MAIL_ACCOUNT ;

-- Create a Database Mail account 

 EXECUTE msdb.dbo.sysmail_add_account_sp 

 @account_name = @V_MAIL_ACCOUNT, 

 @description = @DESCRIPTION, 

 @email_address = @EMAIL_ADDRESS, 

 @replyto_address = @EMAIL_ADDRESS, 

 @display_name = @DISPLAY_NAME, 

 @mailserver_name = @MAILSERVER_NAME;

-- Add the account to the profile 

 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 

 @profile_name = @V_MAIL_PROFILE, 

 @account_name = @V_MAIL_ACCOUNT, 

 @sequence_number = @SEQUENCE_NUMBER;

-- Grant access to the profile to the DBMailUsers role 

 EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 

 @profile_name = @V_MAIL_PROFILE, 

 @principal_id = @PRINCIPAL_ID, 

 @is_default = @IS_DEFAULT;

COMMIT TRAN

 

7) :Script out the logins on server and prepare a list that contains the permissions provided for each login. You are lucky if all users belong to some windows group rather than individual Windows account/SQL accounts for each user.

8) Stop the replication agent if you have replication set up. Use the table sysarticles to get the list of objects that are being replicated only if you chose to use GUI to create replication.Scripting replication makes life easier,incase you wish to use scripts then refer to the link: http://technet.microsoft.com/en-us/library/ms151213.aspx

9) Detach all user defined databases. The sp_detach_db command is listed below.

  sp_detach_db  @dbname=  'AdTestDb' 

               ,@skipchecks= 'false' -- update statistics

               ,@keepfulltextindexfile ='true' --true if you don't want 

                                  --full text search index to be dropped

For more details refer the web link: http://technet.microsoft.com/en-us/library/ms188031.aspx

10) Take the SQL service offline from cluster manager (applicable for clustered environment).If it is a standalone system then shutdown the instance.

Now we can run this command from a command prompt

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 

/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 

/SQLCOLLATION=CollationName

For more details about this command see this link: http://msdn.microsoft.com/en-us/library/ms179254.aspx

Once the command has been executed successfully restart the SQL Services from cluster manager. Check whether the collation has been reset to the required collation by checking server property

 

11) Attach all the user databases.I was for some reason unable to attach the user database using windows account (SQL Server 2008).Use the SQL login to attach the user databases if you come across some errors(probably because this stored proc is deprecated).

EXEC sp_attach_db @dbname = N'AdTestDb'

                 ,@filename1 = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdTestDb.mdf' 

                 ,@filename2 = N'L:\Program Files\Microsoft SQL Server\MSSQL\Data\AdTestDb.ldf'

For more details refer the web link: http://msdn.microsoft.com/en-us/library/aa259611(SQL.80).aspx

12) All the databases that had user defined assemblies should have 'trustworthy' set to true.

ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;

13) Change the server settings using the results obtained in step1.

sp_configure 'name','runvalue'

reconfigure with overrride

14)Recreate the linked servers and logins.check for orphaned users using the command

sp_change_user_login --check orphaned users

For more details refer to this link: http://msdn.microsoft.com/en-us/library/ms175475.aspx

15) Create all the mail profiles(tailor the script given in step6 and use it).

16) Import the SSIS packages to the SQL Server from the file system.

17) Recreate all the jobs.

18) Create the maintenance plan using schedules noted in step4.

19) Setup replication based on the output obtained in step8.

Conclusion

Before you change the collation of production server,do perform a dry run in the development environment and test the jobs,SSIS packages,application etc.If all looks fine then rollout the change into production.The above list isn't exhaustive by any means but will act as a good starting point.

Overview

이건 픽스된 버그 : http://support.microsoft.com/kb/2285068

List of the bugs that are fixed in SQL Server 2008 Service Pack 2

View products that this article applies to.

On This Page

Expand all | Collapse all

INTRODUCTION

This article lists the bugs that are fixed in Microsoft SQL Server 2008 Service Pack 2 (SP2).

Notes

• Other fixes that are not documented may be included in the service pack.
• This list will be updated when more articles are released.

For more information about how to obtain SQL Server 2008 service packs, click the following article number to view the article in the Microsoft Knowledge Base:

968382  How to obtain the latest service pack for SQL Server 2008

Back to the top

MORE INFORMATION

In addition to the fixes that are listed in this article, SQL Server 2008 SP2 contains the hotfixes that were included in Cumulative Update 1 through Cumulative Update 8 for SQL Server 2008 Service Pack 1 (SP1). For more information about the cumulative update packages that are available for SQL Server 2008 SP1, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 SP1 was released 

Back to the top

The bugs that are fixed in this service pack

For more information about the bugs that are fixed in SQL Server 2008 SP2, click the following article numbers to view the articles in the Microsoft Knowledge Base.

VSTS bug number	KB article number	Description
249682	2216454	FIX: Error message when you run the ProcessAdd command after you make changes to the partition on a server that is running SQL Server 2008: "Internal Error: An unexpected exception occurred"
256736	2322209	FIX: Incorrect results if you use parentheses around SELECT in a statement that has a common table expression in SQL Server 2008
258076	2216456	FIX: SQL Server 2008 Service Pack 2 enhancements to the “Operation has been cancelled” error message text in Analysis Services
270561	2216460	Error message when you try to open a project in SQL Server 2008: "The located assembly's manifest definition does not match the assembly reference"
274280	2216461	SQL Server 2008 setup fails to install on a Windows Server 2008-based cluster mount point
284360	955850	Error message when you try to use the binaries from the SQL Server System CLR Types package (SQLSysClrTypes.msi): "Unable to load DLL 'SqlServerSpatial.dll': This application has failed to start because the application configuration is incorrect"
301603	2216465	Slipstream installation of SQL Server 2008 fails if the /PCUSOURCE or /CUSOURCE has a space in the path
317800	2216472	A SQL Server 2005 Analysis Services drillthrough statement result is empty
340066	2216474	FIX: You may experience some loss of data when you use an UPDATE statement with a SET clause that uses the time, datetime2 and datetimeoffset types in SQL Server 2008
347731	2216478	Error message when you run Get-Command in a PowerShell version 2 environment on a server that is running SQL Server 2008: "Object reference not set to an instance of an object"
351286	2216480	FIX: The margins are not retained in a report when the report is exported to Excel on a server that is running SQL Server 2008, and then you preview the report in Excel
351981	2216484	FIX: Error message when you export a report to Excel in Report Manager on a server that is running SQL Server 2008: "Unhandled managed exception: Type = System.ArgumentOutOfRangeException"
357416	2216485	FIX: Resource Monitor enters a non-yielding condition on a SQL Server 2008 server
390126	2216491	The SQL Dumper utility cannot generate error dump files on a SQL Server 2008 failover cluster on an Advanced/Enterprise Failover Cluster install
396527	980671	Correcting the lack of cryptographic salt variation on SQL Server sa login hash
402453	2019402	No mapping between account names and security IDs was done" error when adding a node to a SQL Server 2008 Failover Cluster
426753	2216499	You cannot enroll a SQL Server 2008 database instance into a SQL Server Utility
426753	2253128	You cannot deploy a Data-Tier application to a SQL Server 2008 database instance
427703	2216501	FIX: Multiserver jobs are not downloaded from a SQL Server 2000 in the MSX role on to a SQL Server 2008 in the TSX role
256736	2322209	FIX: Incorrect results if you use parentheses around SELECT in a statement that has a common table expression in SQL Server 2008
524642	2283072	FIX: A hotfix is available that enables SQL Server 2008 R2 Reporting Services to use a 2048-bit RSA key to encrypt a Reporting Services key for FIPS compliancy
370540	2448971	FIX:The locks of a table are not escalated when you insert many rows into a table in SQL Server 2008

Back to the top

Additional issues that are fixed in this service pack

Resolutions to the following issues are also included in SQL Server 2008 SP2.

VSTS bug number	Description
270934	DBCC corruption errors occur during SQL Server 2008 collations when strings that have Jamo sequences that follow the Japanese prolonged sign are used.
271554	FIX: The syscolpars system base table displayed duplicate rows. This fix distinguishes them by adding the procedure number in DBCC checkcatalog.
271708	When query has a filter that references a column in the sys.dm_server_audit_status view, the filter is ignored, and all the rows are returned from sys.dm_server_audit_status.
280004	The asynchronous bucketing target shows incorrect value/string pairs for mapped fields when compared to the information that is available in dm_xe_map_values.
284760	FIX: The Mismatched view can contain multiple views that have different numbers of indexes. This fix forces a deterministic order of views to compare, based on matched functions and on the number of matched indexes.
285377	Users receive an "Access Denied" error message when they navigate to Report Manager locally if UAC is enabled. Adding an updated "Access Denied" message to mention UAC could allow users to address the setting accordingly.
291707	In a given database, using a server cursor that is declared for a select statement that contains any system view may give incorrect results after that database is attached and reattached.
293089	Radial Gauge users who try to use “RateOfChange” or “Integral” as the formula type for the MaximumValue or MinimumValue on the gauge do not see a value in the property grid dropdown list.
296314	FIX: The FailoverAnalysis.sql file is installed during a SQL Server Failover Cluster setup. This script will be called automatically when failure occurs. The SqlDMVScriptTimeout private property controls for how many milliseconds the script can take to finish.
302563	Users receive a validation error when they are upgrading Shared Data Sources that have a space in the name. The error indicates that the name cannot contain spaces.
302707	Installation of Service Pack 1 for SQL Server Business Intelligence Development Studio 2008 fails because of a missing configuration file.
304390	The ring buffer displays incomplete event data (events output from dm_xe_session_targets) when it reaches the size limit for the XML output.
307313	Users cannot return multiple cookies in their HTTP module. This affect users when they try implement custom authentication.
309869	The sqlsos.task_address, sqlsos.worker_address, and sqlsos.scheduler_address Extended Events actions display an incorrect address.
310483	Reporting Services 2008 Designer converts centimeters to inches in the Tablix. This causes a loss of layout precision.
313780	Uninstalling the first of two named AS instances disables the SQL Browser. This causes the second named instance to be unreachable.
319216	Users cannot use stored credentials in their data sources if a Smart Card is required.
322046	A query that has a subselect clause that uses a set expression that does not return any calculated members returns an error saying that subselects that have calculated members are not allowed.
327301	In Analysis Services, setting a dimension default member by using an MDX expression that has a NonEmpty function causes the server to return an internal error when it tries to open the cube.
328752	The sys.dm_os_wait_stats view displays duplicate rows for the MISCELLANEOUS wait type. This can cause problems for monitoring tools.
331630	Integer value gets incorrectly rounded in the SQL Server Reporting Services Reusable Query Designer.
333544	FIX: A new error code (6257) is returned when a user tries to register a CLR assembly that is built against a newer runtime, such as CLR v4. The previous error message (code 6218) was confusing and did not state the actual issue. See the bug for details.
336809	An edge case inserts an identity value outside the current range in "set identity_insert TABLE_NAME on" mode. This issue also occurs in backup/restore operations.
338247	If parameters that are bound by the SQLBindParameter function specify a length of SQL_DATA_AT_EXEC, the SQL Server 2008 Native Client ODC driver causes an access violation if the SQLPutData function specifies a length of SQL_NULL_DATA when the parameter value is supplied for execution.
338999	SQL Server Reporting Services 2008 experiences a connection leak when a user uses the SOAP interface to run and export a report.
347779	SQL Server sometimes generates a "There is insufficient system memory in resource pool 'internal' to run this query" error message. This situation is often preventable.
350670	Users receive a "Page Expired" error message when they try to refersh a rendered report after the report remains idle for one hour.
352788	Error 955 occurs when users tried to configure Management Data Warehouse if they have databases on their server that are not online.
352862	ReportBuilder 2.0 ClickOnce displays a blank report at startup if a user edits the report by using ReportBuilder 2.0 from a SharePoint 12 website.
355222	If MARS is used, the sys.dm_os_tasks view displays the same value in the request_id column for all tasks instead of reporting the correct value associated with each task.
355228	FIX: A new error message indicates the cause of an access violation that occurs if the bucketizer target parameters are set incorrectly.
356127	Incorrect column collation information is returned when BCP.exe retrieves column metadata information.
360584	Subscriptions fail during execution because of an in-memory data structure problem during the attempted email delivery of an MHTLML-rendered report.
368388	BIDS 2008 Report Designer running together with SQL Server Reporting Services 2008 crashes when users resize a table row.
376257	The TO, CC, and BCC fields of the "Report Delivery Options" dialog box are cleared after users cancel or close the "Schedule Details" dialog box to return to "Report Delivery Options."
383747	Collection sets work incorrectly when they collecting performance counters on a cluster if the SQL instance name is the same as the virtual network name.
384800	FIX: Improved and additional information is logged for InvalidReportParameterException in Verbose output if certain conditions occur during parameter processing.
393410	When users clear the selection of one or more months in a monthly schedule, SSRS creates separate schedules for each month that is still selecteded. In some cases, this can cause an "@active_end_date cannot be before @active_start_date" error.
393879	Data-Driven subscription fails if a NULL value is returned when it is mapped to the report parameter, which accepts a NULL value.
394977	Subscription and Delivery issues are difficult to diagnose in the Reporting Services trace file. More verbose logging is required.
415084	Users receive an "Index was out of range. Must be non-negative and less than the size of the collection" error message when they export to PDF.
419740	Users cannot use custom authentication in the SQL Server 2008 Web SKU.
424595	If a user creates a user-defined type and a table valued function in the same transaction, and if the function has a return table containing a column of the user-defined type, a self-deadlock occurs. Additionally, the connection that tries to perform these operations is terminated.
421922	FIX: Support is provided for a maximum of 15,000 partitions in tables and in indexes in the Enterprise, Developer, and Evaluation editions of Microsoft SQL Server 2008 SP2. For more information about how this support can be enabled and disabled, about its limitations, and about some known issues and workarounds, see the "Support for 15,000 Partitions" white paper (http://go.microsoft.com/fwlink/?LinkId=199773).
424804	In Analysis Services, some customers may experience access violation when they use a parent-child hierarchy in the condition of an IIF function.
426631	Using a PeriodsToDate function that has a Null member parameter may cause an "Unexpected exception" error.
429253	An incorrect version number is displayed in SQL Server Management Studio and in SQL Server Reporting Services. This causes confusion because the displayed number does not vary from the RTM version number after an upgrade is performed.
430703	A secondary exception may occur when Analysis Services generates a Watson dump. This is true whether the Watson dump is generated because of an actual exception or because of an explicit request to dump on certain errors. If the secondary exception occurs, the server can experience infinite recursion. This generates many dumps, and then shuts down the server.
431298	FIX: In SSRS 2008 Service Pack 2, the RS add-in for SharePoint 2007 for communicating with SSRS 2008 R2 report server is updated. This enables access to certain functionality in ReportBuilder 3.0, ReportParts, and ATOM Renderer, including the new Report preview in ReportBuilder 3.0.
431301	Processing objects in a database that contains thousands of metadata objects takes a long time to begin importing the data. This occurs because the server has to build a job schedule before the data import begins. Additionally, the algorithm that is used to determine dependencies causes slow performance when many metadata objects exist in the database.
432094	A spatial index cannot be safely created in a table that uses CELL_ID, CELL_ATTRIBUTES or SRID for column names.
448007	In certain conditions, the partitioned column name in error message 11405 may be wrong. This causes an incorrect column name to be printed.
455538	Using cell security together with calculations yields incorrect results when a NON EMPTY clause is used.
456534	Read access violation occurs during a commit process if two transactions run simultaneously: one to modify or create a partition, and one to process a dimension.
471363	When Microsoft Office 12 is integrated with SQL Server 2008 Reporting Services, the SharePoint integration does not default to the Report Builder clickonce URL, preventing Report Builder from starting.
471421	FIX: Localization issue in the Turkish SKU: bread crumbs are now localized on new reportpart library pages.
499162	FIX: An accessibility issue that occurs because the MSAA name of "Symbol" is null. MSAA names are updated and are now discoverable.
503096	FIX: Improved the performance of index creation by DDL statements for tables that have many partitions and that contain no data. Previously, the time required for this process increased in a geometric progression as the number of partitions increased. The time required now increases in a linear progression.
515240	Using table valued parameters for LOB columns may cause an access violation on the server.
520700	MSAA names for the Radial Scale Properties dialog box are incorrect and are not accessibility compliant.
525601	During a check for a granular audit, the algorithm that is used to list any audits that include the event may experience memory leaks of the list of audits. This continues until out-of-memory errors occur and the service has to be restarted to reclaim the memory.

Back to the top

Note If you are upgrading from SQL Server 2008 SP1Cumulative Update 9 or a later version, you must apply a post-SQL Server 2008 SP2 cumulative update after you upgrade to SQL Server 2008 SP2 to obtain all the fixes. 

For more information about the post-SQL Server 2008 SP2 Cumulative Update, click the following article number to view the article in the Microsoft Knowledge Base:

2289254 Cumulative update package 1 for SQL Server 2008 Service Pack 2

Back to the top

REFERENCES

For more information about how to determine the current SQL Server version and edition, click the following article number to view the article in the Microsoft Knowledge Base:

321185  How to identify your SQL Server version and edition

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Back to the top

---

APPLIES TO

Back to the top

Keywords:

kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2285068

Back to the top

Automate Sliding Window Partition Maintenance: Part III

By Hugh Scott, 2010/12/28

 Rate this |   Join the discussion |  Briefcase |   Print

SELECT * FROM partition_info ORDER BY TableName, partitionNumber

PS C:\> ./MergePartition.ps1 <server_name> PartitionMaintTest ps_FACT_DATA_DATE

PS C:\> ./MergePartition.ps1 <server_name> PartitionMaintTest ps_FACT_DATA_DATE "08/01/2009"

Resources:

By Hugh Scott, 2010/12/28