May 12, 2010 at 4:56 am
Hi there,
I have tried using a piece of query-code that I previously used with no problems. The code creates a new table, based on a previous table and looks like this:
-------------------------------------------------------
Select a.*, b.obstime as PrevObsTime, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn
into ReturnSP
from SP a, SP b
where b.obstime=(select max(obstime) from SP where obstime<a.obstime)
-------------------------------------------------------
When I try this for top 10 or top 100, it works absolutely fine and I know that it worked in the past as well with tables as large as the SP table, which I'm basing this on. However, when I try it now, I get an error message like this:
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'Job2' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I guess this may be due to the fact that I'm using the free version of SQL server 2005. When I try to look at the properties of my database, it says that it has reached the maximum initial size of 4096 MB, but this happens also if I try the code in other databases - then they go from initial size of e.g. 12 MB to the 4096...
Does anyone have a clue on what I should do here to get this to work? It'd be a great help to me and I would appreciate it alot!
Best regards,
Martin
May 12, 2010 at 5:19 am
Your db files are >= 4Gb, which is the maximum for sql2005 express edition.
You'll need to free up some space before your engine will be able to allocate it.
You'll have to prepare this action by making the inventory !
- data file size: ____
- log file size: _____
(the sum cannot exceed 4Gb for sql2005 express edition )
Chances are that creating a log backup and shrinking the log file may help out.
Play it safe, start with a FULL database backup !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2010 at 5:32 am
Thanks for the swift reply! What seems strange to me is that I have a few databases in my Databases folder, but the tables within these accumulate to a sum around 200 mb tops when I look at them via the properties option. And also, if it is 4 GB max pr database, then I'd figure it would work to just create a new database to work in? But that doesn't seem to make any difference, though.
As for shrinking the log file, is this doable by point-and-click or is it necessary to do it by coding? The problem is that when I change the "initial size" of the log-file, it seems to just revert back to the original size, regardless of what I do.
Sorry for the dumb questions 🙂
Best regards,
Martin
May 12, 2010 at 5:44 am
Can you script the create database ddl ?
(rightclick on the db-name and then select Script database \ as create \ to query panel and then copy/paste the ddl.)
This will only provide a script like
USE [master]
GO
/****** Object: Database [AdventureWorks] Script Date: 05/12/2010 13:43:58 ******/
CREATE DATABASE [AdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'D:\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 263872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'D:\MSSQL.1\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO
--- and some db settings
I'm interested in the allocated sizes, max sized and filegrowth settings
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2010 at 5:48 am
Yes, I think it looks something like this:
USE [master]
GO
/****** Object: Database [TimeSeries] Script Date: 05/12/2010 13:46:55 ******/
CREATE DATABASE [TimeSeries] ON PRIMARY
( NAME = N'TimeSeries', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TimeSeries.mdf' , SIZE = 4194304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TimeSeries_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TimeSeries_log.ldf' , SIZE = 220160KB , MAXSIZE = 1123473408KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'TimeSeries', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TimeSeries].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TimeSeries] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TimeSeries] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TimeSeries] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TimeSeries] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TimeSeries] SET ARITHABORT OFF
GO
ALTER DATABASE [TimeSeries] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TimeSeries] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TimeSeries] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TimeSeries] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TimeSeries] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TimeSeries] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TimeSeries] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TimeSeries] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TimeSeries] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TimeSeries] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TimeSeries] SET ENABLE_BROKER
GO
ALTER DATABASE [TimeSeries] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TimeSeries] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TimeSeries] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TimeSeries] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TimeSeries] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TimeSeries] SET READ_WRITE
GO
ALTER DATABASE [TimeSeries] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TimeSeries] SET MULTI_USER
GO
ALTER DATABASE [TimeSeries] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TimeSeries] SET DB_CHAINING OFF
If that looks right? Again, thanks for your help! 🙂
May 12, 2010 at 5:55 am
You are creating a db of 4Gb datafile + 2Gb log file .... to large !
Can you give it a try using this ddl:
CREATE DATABASE [TimeSeriesSSC] ON PRIMARY
( NAME = N'TimeSeries', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TimeSeriesSSC.mdf'
, SIZE = 250MB , MAXSIZE = UNLIMITED, FILEGROWTH = 50MB )
LOG ON
( NAME = N'TimeSeries_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TimeSeriesSSC_log.ldf'
, SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 50MB )
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2010 at 6:31 am
Hmm I tried setting up the database using your code, but after running the query from before (this time referring to TimeSeries..SP rather than SP, since the original table is in another database), it still yields the same error. I don't know if it is due to the sheer size (400.000 rows) of the SP table, but it has worked before with similar tables.
Also, when selecting properties on the TimeSeriesSSC database, after running the query, the initial size is 4050 MB of the files, and 100 MB of the logfile.
I don't know if one has to temporarily set up a view table or something to get it to work...
May 12, 2010 at 7:11 am
martinfalch (5/12/2010)
Hmm I tried setting up the database using your code, but after running the query from before (this time referring to TimeSeries..SP rather than SP, since the original table is in another database), it still yields the same error. I don't know if it is due to the sheer size (400.000 rows) of the SP table, but it has worked before with similar tables.Also, when selecting properties on the TimeSeriesSSC database, after running the query, the initial size is 4050 MB of the files, and 100 MB of the logfile.
I don't know if one has to temporarily set up a view table or something to get it to work...
You db has grown to 4GB due to the script.
Analyse your queries to see how much data it will actually produce !
Without having the full ddl/sql of the process you're performing, I cannot estimate why or what is going wrong.
You can script the ddl from the gui using :
Rightclick on a dbname, select "tasks" \generate scripts \ ...
Be sure to script all objects of the selected db.
(or only select the involved objects, but also include indexes, triggers, ...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2010 at 7:50 am
Ok, I'll try looking into it 🙂 Thank you very much for all the help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply