Have you ever had the above error message? Wondered how to sort it out? Well I am going to show you how.
Today I was confronted by the above error when a test migration database which was setup with replication was loaded with simulated data. The migration source environment was not replicated so I did a check of the length of the data using LEN but thought this was inappropriate as we could solve the problem for a short period before facing the same problem again in testing. I thought it would be best to note the current configuration and then put the max value and note that we would have to change this configuration when it was loaded with data from production.
I checked the current configuration using the below script and screen captured it for future reference.
EXEC sp_configure ‘show advanced options’, 1 ;
EXEC sp_configure ‘max text repl size’;
I then decided that I would give the maximum value until later in the project when I could more fine tune this value.
EXEC sp_configure ‘show advanced options’, 1 ;
EXEC sp_configure ‘max text repl size’, -1 ;
This can be done easily via SSMS by going on the server > Properties > Advanced > Max text replication Size > change value to -1.
A quick snippet for some newbie PowerShell developers whilst they start getting their teeth into the ISE is there are two easy ways of clearing the screen. I ahdnt thought about sharing this information but I saw a developer simply closing the ISE so that he could start afresh. I asked him why and he didn’t know any other way and hadn’t spent any time exploring the ISE or learning many PowerShell commands.
If you ever have a screen which you want to clear like below.
You can either type cls which will clear the screen or if you forget that syntax use the clear console pane button highlighted below.
Have you ever wanted to be able to easily read through the help system in PowerShell? Wanted to take advantage of your second screen whilst developing in PowerShell? Well I am going to show you how.
Whenever I have used PowerShell I had a love-hate relationship with the help system. Whilst it helps me out a lot by making it easy to understand and use PowerShell commands it also is a pain having to scroll up and down to understand the syntax and go through the notes.
However, I Just found an excellent new feature ( at least to me ) which opens PowerShell into another window using the PowerShell command -ShowWindow which is in the example below.
help Get-Host -ShowWindow
That then opens the below screen which is a much more user friendly Help box.
Have you ever wondered how to update the PowerShell help system? Wondered how to update it more than once in a 24 hour period? Needed to update the help file of a system not connected to the internet? Well I am going to show you how.
Whenever I jump onto any new server the first thing I do is check what PowerShell version is on the box via $psversiontable or Get-Host. Then I usually would ask the Windows Admins to upgrade to the latest version.
The second thing which I do is update the Help system. In version 2.0 this involves downloading the latest Help file. In PowerShell 3.0 they have made it super easy and all you have to do is run the command update-help
You will then get the below screen running whilst update runs.
This can be run once every 24 hours but if you do have a need to update more frequently you will need to run the below command.
Whilst this seems easy this is only good for those Servers which are connected to the internet. If you are looking to do it on a Server which is not connected to the internet then you will need to use the save-help command. This saves a copy of the help file which can be copied to other servers.
Save-Help -Force -DestinationPath C:PS
Please note I only used the -Force command for illustration purposes as I wanted to do the update more than twice in a 24 hour period for the purposes of this article.
Have you ever wondered what PowerShell version your machine was running? Needed to quickly check the PowerShell version to see whether it had successfully upgraded? Well I am going to show you how.
Today I was on one of my creaky old SQL Servers when I started doing some inventory checks to see what level the SQL 2000 and Windows Server 2003 were patched to and do some other inventory checks . Usually I would use some PowerShell scripts but as this Server exists in an old secured environment I could not us them and wondered whether it would be worth going through the motion of getting the scripts screened and approved. Before that I thought I should at least check what version of PowerShell was on the box and having never used PowerShell with Server 2003 or SQL 2000 thought this would be a good bit of fun.
I saw the PowerShell icon and felt uplifted and started it. I then typed $psversiontable and got an error. I had no access to google so it took me a few minutes to remember that the command ‘get-host’ had the information required. When it returned version 1.0 I decided to just stick with the current methods as I didn’t think it would be worth my time checking if my scripts would work in 1.0 when everything I had developed on version 2.0 and higher.
So in the future I would recommend using either of the two below commands to get the version number.
Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.
Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.
SELECT * FROM SYS.ALL_OBJECTS WHERE [name] LIKE '%dm_%' AND [type] IN ('V', 'TF', 'IF') ORDER BY [name]
This script will only run in SQL2005 ( SQL 2000 does not include these features) and above.
In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.