SQL Server 2008 :: File Group Is Running Out Of Space Frequently

Sep 1, 2015

We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.

Error:

The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.

tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited

View 9 Replies


ADVERTISEMENT

File Group Is Running Out Of Space Frequently

Sep 1, 2015

We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.

Error:
The file group "PRIMARY"  for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space. 
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB 
templog  Initial Size : 5,475 MB    Growth: By 10 percent, Unlimited

View 8 Replies View Related

File Group Is Running Out Of Space?

Jul 21, 2015

I am currently SQL admin since our ordinary SQL folks are at summer vacation.

Yesterday SCOM alerted that File Group is running out of space on one not so critical database "The file group "PRIMARY" for the database "loganalys" in SQL instance "MSSQLSERVER" on computer "sqlserver2" is running out of space".

I logged in to the SQL server and checked the database in question. It is a very large DB with a size  of 577 GB.The storage on which the database files resides has 123 GB free space so that isn't the issue.

The database is set to autogrowth 1 MB at a time (unrestricted) so that doesn't seem to be the issue either.However, in the database properties under General, it says Space avaliable: 570 MB.

I guess that this may be the issue, that this is under some treshold for SCOM to alert on.I have looked at the other databases and they have everything between 0,25MB to 270MB space avaliable (they are all set to autogrowth 1 MB, unrestricted).

I am not sure what this means and if I have to do something about it?

View 6 Replies View Related

SQL Server 2008 :: Log File Space Is 5 Times The Data File

Mar 16, 2015

one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.

View 9 Replies View Related

TempDB Log File Running Out Of Free Space While Running DBCC CheckDB On Large Database

May 28, 2015

In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.

What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.

View 3 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 Replies View Related

SQL Server 2008 :: Moving Tables To New File Group

Mar 16, 2015

I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.

So, how do I get the free space out of the data file? - I've decided to:

1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables

There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?

View 8 Replies View Related

SQL Server 2008 :: Running WinSCP In SSIS Task / Bat File

Apr 29, 2015

I'm trying to call WinSCP in a SSIS Execute Process Task using a .bat file to automate. SSIS is returning a generic failure error message ("Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "MyServerC$Program Files (x86)WinSCPWinSCP.exe" "-script=MyNASShareWinSCPFTP.bat " at "", The process exit code was "1" while the expected was "0"."). So now I'm trying to run the .bat file by itself. Unfortunately, the command prompt rushes by so fast that I can't see what the server is doing.

I can't find anything on the WinSCP site that indicates how to slow down the .bat file processing or log the remote server responses. I do see how to use the /log switch when using the interactive command line console, but that's not what I want to do.

View 9 Replies View Related

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

File Group Space Error

Jul 20, 2005

Hi,Received the following error during index creation of the tables. Thedata & log files are set to 'unrestricted growth' and enough spaceavailable on the disk. Any reasons?___________Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocatenew page for database 'Ultimareports'. There are no more pages availablein filegroup PRIMARY. Space can be created by dropping objects, addingadditional files, or allowing file growth___________ThanksJohn Jayaseelan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

File Group Free Space--urgent

May 23, 2000

Hi,
In sql 7.0 , i would like to create a database with the size of 10Gb, in my server couple of databases already exist.
How do i know how much free space is there in File group.
we are having only one file group i.e PRIMARY.
Could anyone pls tell me about this.
Thank u.

--kavira

View 2 Replies View Related

SQL 2012 :: Free Space After Moving Tables From One File Group To Another

Aug 13, 2014

Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?

View 2 Replies View Related

Transact SQL :: How To Transfer Tables From One To Another File Group In 2008

Jan 17, 2012

I have few issues regarding the transfer of the tables from one file group to another file group  in SQL 2008 and also How can we  backup and restore the particular database based on file group level.

Let’s say I have a tables stored within the different FG. such as

Tables                                                    
                                                                         
File group Dimension tables             Primary Fact tables                                               
FG1 ...                                                
FG2…
zzz_tables                                                DEFAULT_FG    
dim.table1                                                                                                                          DEFAULT_FG
dim.table2                                                                                                                          DEFAULT_FG
…                                                                                                                                             ….

Here all I want to transfer the dim.table1 ,dim.table2  from  DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2  from one FG to another .I have tried somewhat but I couldn’t get the exact way.Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing  the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?

View 15 Replies View Related

SQL Server 2000 Running Out Of Space.

May 18, 2006

Hi, we have a SqlServer instance in production containing around 10databases.It has just been realised that all the db's are held on the small c:partition with only a gig or so of space left.On the server there is another partition and another hard disk bothwill ample space (few hundred gigs).What would be the best way of getting the data onto the otherpartitions with minimal impact on the applications. Can we move theprimary data files for each db? Should we just create secondary datafiles on the big partitions for all the db's? Is there a method ofmoving all the data at once?Any ideas on how we should approach this?(ps we dont have control of the sqlserver its outsourced, so simplerthe better).Thanks,Jim

View 2 Replies View Related

SQL Server 2008 :: DB Showing Space Available 0.00 MB

Mar 11, 2015

I've just checked the properties of one of our DB's running on SQL2008 & the properties within SSMS are showing 0.00MB space available.

The DB is setup using the simple Recovery model & is being backed up nightly.

The Data file has been set at an intial size of 9449MB with Autogrowth by 10 percent unrestricted growth.

The Log File has been set at an intial size of 14199MB with Autogrowth by 10 percent unrestricted growth.

I have checked the SQL logs and no errors are being reported. There is 79.8gb available on the partition where the DB resides.

View 0 Replies View Related

SQL Server 2008 :: How To Convert Free Space Value To MB Or In GB

Mar 9, 2015

wmic logicaldisk where "DeviceID='C:'" get FreeSpace /format:value

FreeSpace=231379468288

View 1 Replies View Related

SQL Server 2008 :: Track Unallocated Space On Database?

Apr 1, 2015

I am trying to track unallocated space (because on my database growth is not kicking in) So using below query but not working!!! Is there any way I can track 'database size' and 'unallocated space' on a single database?

create table A(
Rundate DATETIME NOT NULL DEFAULT(GETDATE())
,DatabaseName varchar(100)
,Database_size varchar (100)
,unallocatedSpace varchar (100)
,reserved varchar(100)

[code].....

exec AdventureWork..sp_spaceused ------not working------

View 2 Replies View Related

SQL Server 2008 :: Not Finding A Record With A Space In The Middle Of String

May 19, 2015

Ok following scenario

create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')

So if execute the following

select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'

I get both records,but if I do this

select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)

I only get AIRLBEOF3565

So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.

View 4 Replies View Related

SQL Server 2008 :: How To Delete Or Drop Index To Free Up Space

May 31, 2015

I am new to mssql server. There is a table on one of my databases that occupies a lot of space. And the space usage is as follows: (all values in KB)

reserved: 42329064
data: 16272288
index: 26050032
unused: 6744

This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)

I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.

View 3 Replies View Related

SQL Server 2008 :: Table Space Growth Based On Each Year

Oct 28, 2015

Does there a way or script to find the rows, space used by the specific tables based on every year.

View 9 Replies View Related

SQL Server 2008 :: Deleting Data (rows) From Table To Reclaim Space?

Feb 11, 2015

I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.

If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?

View 8 Replies View Related

SQL Server 2008 :: Estimate Forecast Space For Non-clustered Index On A Table

Apr 22, 2015

What is the best way to forecastestimate space for non-clustered index on a table?

Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB

Note: Test123 already has clustered index and 2 non clustered indexes.

View 7 Replies View Related

SQL Server 2008 :: Handle Space Between Multi-value Parameter Values In SSRS?

Jul 8, 2015

how to handle space between multi-value parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.

View 3 Replies View Related

SQL Server 2008 :: Group By Total Sum

Oct 31, 2015

Despite the products are different, I want to get the sum of all products according to customer card.(client ref)

select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX

View 1 Replies View Related

Transact SQL :: Running Total In Server 2008

Nov 20, 2015

Is it possible to assign to a variable, then add to it later on?  When I run the below, all I get is 3 rows affected I never see the value printed.  What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers
= 6
Create Table #Test ( randarnumbers int )
Insert Into #Test Values ('2'), ('1'), ('3')
Declare @sumofallnumbers int, @nbr int
Declare c1 Cursor For
Select randarnumbers
FROM #Test

[code]..

View 6 Replies View Related

SQL Server 2008 :: Field As Group Header?

Jan 29, 2015

I have a table returning results like that

Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1

[code]....

But I want to look like that

Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1

[code]....

View 3 Replies View Related

SQL Server 2008 :: Getting Row Numbers For Each People Group?

Feb 18, 2015

I have a set of rows in a table like for example

Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34

Now I want to use Row Number function or any way where I can get the result as below

Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2

View 3 Replies View Related

SQL Server 2008 :: Using Expression In GROUP BY Clause

Feb 23, 2015

I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:

SELECT StartTime + Offset, min(Sensor), max(Sensor)
FROM SensorData
WHERE SensorID = @SensorID AND
StartTime + Offset > @BeginTime AND
StartTime + Offset < @EndTime
GROUP BY (StartTime + Offset) / 100
ORDER BY StartTime + Offset

What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.

Under MS SQL Server 2008, SP3, I get the two following error messages:
Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 9 Replies View Related

SQL Server 2008 :: How To Remove Group By Clause

Mar 5, 2015

SELECT DISTINCT

'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
-- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--

[Code] ....

The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.

View 4 Replies View Related

SQL Server 2008 :: Get Max Of Group No Matter Currency

Aug 5, 2015

I have this data below :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)

DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

get this data by running the following query :

select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt

what's needed is to get the earliest record all times, no mater the currency :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

View 3 Replies View Related

SQL Server 2008 :: Log Shipping Report Not Running After Restart?

May 13, 2015

When trying to open the log shipping status report, I get an error: user must be in the master database. The jobs appear to be running correctly.

View 0 Replies View Related

SQL Server 2008 :: Queries Running Against To A Table In Database

Jun 15, 2015

How can we get most frequent queries that are running against to a table in our database?

View 3 Replies View Related

SQL Server 2008 :: Job Details In Running Schedule In Minutes

Aug 6, 2015

In my requirement,My production server running more then 5 jobs . I want details in running schedule in minutes. For example

1) First Job

I am configured every one hour.

2) Second job

Configured in every 30 min

3) Third job

Configured every 60 min

4) Fourth Job

Configured every 24 hrs

I want the result

SNOJOBNameRunning Schedule(in Min)
1First JOB 60min
2Second JOB 30 min
3Third JOB 60min
4Fourth JOB 1440min

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved